I have the following query where I have a table which is storing from information with a delimiter
SQL version – Microsoft SQL Azure (RTM) – 12.0.2000.8
x
DECLARE @commanTable TABLE
(
CommaId NVARCHAR(MAX),
Name NVARCHAR(500)
)
DECLARE @commanTable1 TABLE
(
CommaId INT,
Name NVARCHAR(500)
)
INSERT INTO @commanTable VALUES ('2324205.3933251.7336404', 'Test1'),
('2324206.3933252.7336405', 'Test2')
INSERT INTO @commanTable1 (CommaId, Name)
SELECT value, Name
FROM @commanTable CROSS APPLY STRING_SPLIT(CommaId,'.');
SELECT * FROM @commanTable1
Getting the following results
Where I need the results to be
Advertisement
Answer
If you only interested in the first value, you don’t need to use STRING_SPLIT()
. You can simply use charindex()
to find the first delimiter and use left()
to extract it
SELECT *, left(CommaId, charindex('.', CommaId) - 1)
FROM @commanTable
Edit : if you always wanted the nth value, you can do a cascade charindex(). Provided that the n is not too huge. Else use the function that I provided in the comment.
SELECT *,
item1 = left(CommaId, p1.p - 1),
item2 = substring(CommaId, p1.p + 1, p2.p - p1.p - 1)
FROM @commanTable t
cross apply
(
select p = charindex('.', CommaId)
) p1
cross apply
(
select p = charindex('.', CommaId, p1.p + 1)
) p2