asked
Split string based on a pattern
Hello, Does anyone know how to separate a string based on a number followed by a period pattern into rows? I need to get "qwer qwer. qwer 1 asdf1. asdf 2. asdf sdf4 1. asdfl 4... 5a. 22.asdf" into: Column A Column B 1, "qwer qwer. qwer 1 asdf" 2, " asdf " 3, " asdf sdf4 " 4, " asdfl " 5, "... 5a. " 6, "asdf" Any help is appreciated, Thanks.
DECLARE @s varchar(max) SET @s = '55.1.qwer qwer. qwer 1 asdf1. asdf 25767654. asdf sdf4 134236.5. asdfl 4243... 5a. 24352.asdf' ;WITH cte AS ( SELECT [Column A] = CASE WHEN LEFT(@s,2) LIKE '[0-9][.]' OR LEFT(@s,3) LIKE '[0-9][0-9][.]' THEN 0 ELSE 1 END, [Column B] = CASE WHEN PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(@s,1,PATINDEX('%[0-9][.]%',@s)-1)))=0 THEN '' ELSE SUBSTRING(@s,1,PATINDEX('%[0-9][.]%',@s) -PATINDEX('%[^0-9]%',REVERSE(SUBSTRING(@s,1,PATINDEX('%[0-9][.]%',@s)-1)))) END, [Tail] = SUBSTRING(@s, PATINDEX('%[0-9][.]%',@s)+2,LEN(@s)) UNION ALL SELECT [Column A] + 1, CASE WHEN PATINDEX('%[0-9][.]%',[Tail]) > 0 THEN CASE WHEN PATINDEX('%[^0-9]%',REVERSE(SUBSTRING([Tail],1,PATINDEX('%[0-9][.]%',[Tail])-1)))=0 THEN '' ELSE SUBSTRING([Tail],1,PATINDEX('%[0-9][.]%',[Tail]) -PATINDEX('%[^0-9]%',REVERSE(SUBSTRING([Tail],1,PATINDEX('%[0-9][.]%',[Tail])-1)))) END ELSE [Tail] END, CASE WHEN PATINDEX('%[0-9][.]%',[Tail]) > 0 THEN SUBSTRING([Tail],PATINDEX('%[0-9][.]%',[Tail])+2,LEN([Tail])) ELSE '' END FROM cte WHERE LEN([Tail]) > 0 ) SELECT [COLUMN A], [COLUMN B] FROM cte WHERE [COLUMN A] > 0
DECLARE @StringToParse VARCHAR(100), @EndOfRecord INT DECLARE @Records TABLE ([COLUMN A] INT IDENTITY(1, 1), [Column B] VARCHAR(80)) SELECT @StringToParse = 'qwer qwer. qwer 1 asdf1. asdf 2. asdf sdf4 1. asdfl 4... 5a. 22.asdf' WHILE LEN(@StringToParse) > 0 BEGIN SELECT @EndOfRecord = PATINDEX('%[0-9].%', @StringToParse + '2.') INSERT INTO @Records ([Column B]) SELECT LEFT(@StringToParse, @EndOfRecord - 1) SELECT @StringToParse = STUFF(@StringToParse, 1, @EndOfRecord + 1, '') END SELECT * FROM @Records /* COLUMN A Column B ----------- -------------------------------------------------------------------------------- 1 qwer qwer. qwer 1 asdf 2 asdf 3 asdf sdf4 4 asdfl 5 .. 5a. 2 6 asdf (6 row(s) affected) */
