How to split strings in SQL Server
I have the following input:
Data
-----
A,10
A,20
A,30
B,23
B,45
开发者_StackOverflow社区
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?
SELECT substring(data, 1, CHARINDEX(',',data)-1) col1,
substring(data, CHARINDEX(',',data)+1, LEN(data)) col2
FROM table
I know the points has already been given, going to post it anyway because i think it is slightly better
DECLARE @t TABLE (DATA VARCHAR(20))
INSERT @t VALUES ('A,10');INSERT @t VALUES ('AB,101');INSERT @t VALUES ('ABC,1011')
SELECT LEFT(DATA, CHARINDEX(',',data) - 1) col1,
RIGHT(DATA, LEN(DATA) - CHARINDEX(',', data)) col2
FROM @t
if the values in column 1 are always one character long, and the values in column 2 are always 2, you can use the SQL Left and SQL Right functions:
SELECT LEFT(data, 1) col1, RIGHT(data, 2) col2
FROM <table_name>
declare @string nvarchar(50)
set @string='AA,12'
select substring(@string,1,(charindex(',',@string)-1) ) as col1
, substring(@string,(charindex(',',@string)+1),len(@string) ) as col2![my sql server image which i tried.][1]
it is so easy, you can take it by below query:
SELECT LEFT(DATA, CHARINDEX(',',DATA)-1) col1,RIGHT(Data,LEN(DATA)-(CHARINDEX(',',DATA))) col2 from Table
精彩评论