Search String

• raym

  SSCrazy

  Points: 2213

  More actions

  November 22, 2007 at 2:42 pm

  #180690

  How Can I Select Only The Values After The 1st Space And Stop Only When It Gets To The Next Space

  I.e The spaces are not consistant. I other words its not always one space, sometimes its double or more...

  98402

  6060

  9990

  759

  Select '80 98402 3 997 -0.250'

  Select '01 6060 3 997 -20.000 Cb'

  Select '01 9990 9991 997 -127.910'

  Select '01 759 9991 997 -0.560'

  Select '0000058916 00000074000039708'

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  November 22, 2007 at 5:41 pm

  #753479

  I suppose you could hardcode some SUBSTRING lookups using CHARINDEX and the like... but, I like to remain, well, "flexible"... 😉 Soon as you ask for the second part, you'll get a requirement to return the third 😉

  My favorite tool to solve such problems with splitting strings is the Tally table... A Tally table is nothing more than a table that contains a single column of very well indexed sequential numbers, usually starting at "1" and going on to some planned out number. It has very many uses and you should probably make a permanent Tally table somewhere in your database.

  Here's how to make one...

  --===== Create and populate the Tally table on the fly

  SELECT TOP 11000 --equates to more than 30 years of dates

  IDENTITY(INT,1,1) AS N

  INTO dbo.Tally

  FROM Master.dbo.SysColumns sc1,

  Master.dbo.SysColumns sc2

  --===== Add a Primary Key to maximize performance

  ALTER TABLE dbo.Tally

  ADD CONSTRAINT PK_Tally_N

  PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

  --===== Allow the general public to use it

  GRANT SELECT ON dbo.Tally TO PUBLIC

  After that, problems such as what you have specified become incredibly easy to solve and maintain some pretty good performance... it also allows you to write "flexible" code...

  --===== Declare a variable to hold the delimiter and desired "word" to return

  DECLARE @Delim CHAR(1)

  SET @Delim = ' '

  DECLARE @Part2Return INT

  SET @Part2Return = 2

  --===== Do the split with a count

  SELECT Val = SUBSTRING(@Delim+h.SomeString+@Delim, t.N+1, CHARINDEX(@Delim, @Delim+h.SomeString+@Delim, t.N+1)-t.N-1)

  FROM dbo.Tally t

  RIGHT OUTER JOIN --Necessary in case SomeString is NULL

  dbo.StringTest h

  ON SUBSTRING(@Delim+h.SomeString+@Delim, t.N, 1) = @Delim

  AND t.N < LEN(@Delim+h.SomeString+@Delim)

  AND t.N-LEN(REPLACE(LEFT(@Delim+h.SomeString+@Delim,t.N), @Delim, '')) = @Part2Return

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)