asked
Split a string based on Delimter
I am trying to write a query that will allow me split a string based on the "." before the 100th character. So if the string is 120 charcaters the "." is at character 90 I would only take the first 90 charcaters in the first record and the the rest of the string would go into anothe record for that id. As well if the string is 320 characters the record would get have to be divided at least 3 times because the datasize allowed is 100 characters. I tried this Case Statement which divides based on the first 100 charcaters. But I need to add to it to allow me to sleect cut it off at lets say character 90 if thats when the "." before the 100th charcater is. CASE WHEN LEN(ColumnA) < 100 THEN ColumnA ELSE SUBSTRING(ColumnA,101,LEN(ColumnA) ) END , I am cleaning up data. The data needs to be varchar(100) but some of my strings are more than 100 characters. So I need to split the ID into multiple records to fit 100 charcaters in the column. BUt I don't want to just cut it off at 100 because then th next record won't make sense. so I want to cut it off at the PERIOD before the 100th charcater. Below lets pretend the leter c is the 100th character Original ID BadData 1 aaaaa. aaaaaaaa. bbcbbbbbb How I want it: ID BadData 1 aaaaa. aaaaaaaa. 1 bbcbbbbbb
I'm not clear on what you need. Can you define the input data type and the output data type(s) more clearly please? Is the input to be split on a character or on a length, or both? Does output have to go to 1,2,3 or more columns?
I am cleaning up data. The data needs to be varchar(100) but some of my strings are more than 100 characters. So I need to split the ID into multiple records to fit 100 charcaters in the column. BUt I don't want to just cut it off at 100 because then th next record won't make sense. so I want to cut it off at the PERIOD before the 100th charcater. Below lets pretend the leter c is the 100th character Original ID BadData 1 aaaaa. aaaaaaaa. bbcbbbbbb How I want it: ID BadData 1 aaaaa. aaaaaaaa. 1 bbcbbbbbb
This might be something you can work on to get what you want but until we get better information we cant go much further DECLARE @data TABLE ( YourData VARCHAR(500) ) INSERT @data ( [YourData] ) SELECT TOP 20 REPLICATE('a', ABS(CHECKSUM(NEWID())) % 15) + '.' + REPLICATE('a', ABS(CHECKSUM(NEWID())) % 50) FROM [sys].[syscolumns] AS s UPDATE @data SET [YourData] = d.[YourData] + + '.' + REPLICATE('a', ABS(CHECKSUM(NEWID())) % 50) FROM @data AS d WHERE PATINDEX('%.%',d.[YourData]) < 10 SELECT [d].[YourData] , CASE WHEN LEN(d.[YourData]) - LEN(REPLACE(d.yourdata, '.', '')) = 0 THEN 'No value found' WHEN LEN(d.[YourData]) - LEN(REPLACE(d.yourdata, '.', '')) = 1 THEN LEFT([d].[YourData], PATINDEX('%.%', [d].[YourData]) - 1) WHEN LEN(d.[YourData]) - LEN(REPLACE(d.yourdata, '.', '')) > 1 THEN 'More than one value found' END AS Value_to_the_left FROM @data AS d
10 People are following this question.
