Menu
I have the following input:
Data ----- A,10 A,20 A,30 B,23 B,45 Expected output:
col1 Col2 ---- ----- A 10 A 20 A 30 B 23 B 45 How can I split the string to produce the desired output?
447
The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument. FROM STRING_SPLIT( 'An example sentence.
Used WHERE CHARINDEX(' ',ltrim(rtrim(fieldname))) = 0 which works fine ! @huMptyduMpty - You got it. =0 will give you items with one word, >0 will give you items with two words -- and yes, if you have leading in or trailing spaces those will need to be removed.
STRING_SPLIT inputs a string that has delimited substrings and inputs one character to use as the delimiter or separator. Optionally, the function supports a third argument with a value of 0 or 1 that disables or enables, respectively, the ordinal output column.
The STRING_SPLIT() function is a table-valued function that splits a string into a table that consists of rows of substrings based on a specified separator. In this syntax: input_string is a character-based expression that evaluates to a string of NVARCHAR , VARCHAR , NCHAR , or CHAR .
SELECT substring(data, 1, CHARINDEX(',',data)-1) col1, substring(data, CHARINDEX(',',data)+1, LEN(data)) col2 FROM table If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
