Geting the first characters before the space

• foxjazzG

  SSCrazy

  Points: 2221

  More actions

  March 25, 2011 at 1:08 pm

  #235766

  I have data in a field

  'adsf jjjj'

  'asdf2'

  I want to select and get this

  'asdf'

  asdf2'

  Any ideas?

• skt5000

  SSC-Addicted

  Points: 479

  More actions

  March 25, 2011 at 1:35 pm

  #1303861

  declare @var varchar(10)

  set @var = 'asdf2'

  select case when charindex(' ',@var) > 0 THEN left(@var,charindex(' ',@var)-1) else @var end

  Jim

• WayneS

  SSC Guru

  Points: 95460

  More actions

  March 25, 2011 at 8:00 pm

  #1303916

  Use the DelimitedSplit8K function. Here is the latest version of the Delimited Split Function.

  And you would use it like this:

  -- make and populate a table to hold the test data

  DECLARE @test-2 TABLE (RowID INT IDENTITY, Col1 varchar(50));

  INSERT INTO @test-2

  SELECT 'adsf jjjj' UNION ALL

  SELECT 'asdf2';

  WITH cte AS

  (

  -- split the data by the slash

  SELECT t.RowID,

  t.Col1,

  ds.Item,

  ds.ItemNumber

  FROM @test-2 t

  CROSS APPLY dbo.DelimitedSplit8K(col1, ' ') ds

  )

  -- now for the test numbers, get the max row for each number

  SELECT RowId,

  Col1,

  Item

  FROM cte

  WHERE ItemNumber = 1;

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• tcampau

  Valued Member

  Points: 69

  More actions

  March 29, 2011 at 8:08 am

  #1304887

  Here is the K.I.S.S. Version...

  SELECT *, CASE WHEN(CHARINDEX(' ',test)=0) THEN test ELSE LEFT(test,CHARINDEX(' ',test)) END FROM tablename

  This should work for you.