November 7, 2012 at 3:16 pm
Hi all,
I need to split the following string: 'A,B,C'
In Oracle I'd use instr() function to get the position of commas and pull the values in between.
Sql Server has charindex() function, but I can't get how you'd specify the position of the 2nd comma?
Thanks,
November 7, 2012 at 3:26 pm
this would show what ever was between the 1st comma and the second
declare @string varchar(20)
set @string='A,B,C'
Select substring( SUBSTRING(@string,charindex(',',@string)+1,99),0,charindex(',',SUBSTRING(@string,charindex(',',@string)+1,99)))
EDIT
also take a look at this post
http://www.sqlservercentral.com/Forums/Topic1368056-391-1.aspx#bm1368081
***The first step is always the hardest *******
November 7, 2012 at 3:32 pm
what's 99 for?
November 7, 2012 at 3:36 pm
And a link to my favorite string splitter. Don't leave home without it...
http://www.sqlservercentral.com/articles/Tally+Table/72993/
November 7, 2012 at 3:45 pm
Thank you David,
I think your article is great, but I think it's to much for me to go through to be able to split 'A,B,C' without hardcoding positions.
November 7, 2012 at 3:48 pm
99 reflects the potential length of a string between the 1st comma and the second comma
Substring syntax
Substring (expression,startpoint,lenght)
99 represents thee Length of the expression
build the query up have a play with substring
here another example with multiple results
create table col (col varchar(255))
insert into col select 'A,B,C' union all
select 'AA,BBBB,CCC'union all
select 'AAA,BBBBBBBBBBBBBBBBBBBBBBBBBBBBB,CCC'union all
select 'AAAAAAA,BBBBBBBB,CCC'
Select substring( SUBSTRING(col,charindex(',',col)+1,99),0,charindex(',',SUBSTRING(col,charindex(',',col)+1,99)))
from #t1
***The first step is always the hardest *******
November 7, 2012 at 4:00 pm
Thank you for an example.
I ran it and I think I understand it, but my question still is: can you pick a value between 2 commas without hard-coding the length?
Could you just find a position of a first and second comma?
November 7, 2012 at 4:45 pm
Once you create the function, it's really not that hard...
declare @dept varchar(200)
set @dept = 'A,B,C'
select item from [DelimitedSplit8K](@dept,',')
item
A
B
C
The article is by Jeff Moden (who had probably forgotten more about this stuff than I'll ever know).
November 8, 2012 at 3:01 am
this gives you the charindex of comma 1 and comma 2
select charindex (',',col) as comma1,charindex(',',SUBSTRING(col,charindex(',',col)+1,99))+charindex (',',col) as comma2
from #t1
***The first step is always the hardest *******
November 8, 2012 at 3:09 am
try this one
declare @string varchar(20)
set @string='e33,B6661,C1'
select substring(@string,charindex(',',@string)+1,charindex(',',@string,charindex(',',@string)+1)-charindex(',',@string)-1)
November 8, 2012 at 3:26 am
If you need to split value in multiple rows (eg. in SELECT query), You better to use [DelimitedSplit8K] or some CLR splitter.
For single value split (let say you pass the comma separated string into stored proc), any LOOP-based splitter will be fine, and actually proper-written LOOP-based splitter will even win over tally-table based DelimitedSplit8K.
If you know that number of comma separated values is constant and it's just 3, you can even use this:
DECLARE @STR VARCHAR(1000) = 'AAAAAA,BBBBBBBB,CCCCCCCCCC'
--Return as three separate values (you can set three separate variables here)
SELECT PARSENAME(S,3) Val1
,PARSENAME(S,2) Val2
,PARSENAME(S,1) Val3
FROM (SELECT REPLACE(@str,',','.')) S(S)
--Return as table (you can join to it, just wrap it inn CLR or use it as subquery))
SELECT PARSENAME(S,I) Val
FROM (SELECT REPLACE(@str,',','.')) S(S)
CROSS JOIN (VALUES (1),(2),(3)) I(I)
ORDER BY I DESC
Please note, the above code assumes that there is no "." in a string, but it can be modified to support it.
November 8, 2012 at 11:36 am
Thanks Eugene and everyone else.
I have plenty of options now. I really like Eugene's example of using PARSENAME().
I think I am going to use it as it fits my case.
I did came up with the following as well:
declare @string varchar(20)
set @string='XyZ,KtrL,AbC'
select
@string,
SUBSTRING(@string,1,charindex(',',@string)-1) first_value,
substring(@string,charindex(',',@string)+1,charindex(',',@string,charindex(',',@string)+1)-charindex(',',@string)-1) second_value,
reverse(substring(REVERSE(@string),1,charindex(',',REVERSE(@string))-1)) third_value
November 9, 2012 at 9:44 am
The DelimitedSplit8k option has been shown to be among the most efficient methods for splitting strings. Another less-complicated but les efficient option is below. Whatever method you choose, I'd recommend a Table-Valued Function so you can JOIN or CROSS APPLY to the split data.
CREATE FUNCTION [dbo].[tvfParseDelimitedString]
(
@s-2 NVARCHAR(MAX) -- Delimited input string
,@Split CHAR(1) -- Delimiter used for the input string
)
RETURNS @Table TABLE
(
[ID] INT NOT NULL IDENTITY(1,1)
,[Value] NVARCHAR(MAX) NULL
,PRIMARY KEY ([ID])
,UNIQUE ([ID])
)
BEGIN
DECLARE @X XML
SET @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')
INSERT INTO @Table
SELECT T.c.value('.','NVARCHAR(MAX)') AS [Value]
FROM @X.nodes('/root/s') T (c)
RETURN
END
GO
Here are examples of how to call this function and the DelimitedSplit8k function. These functions produce tables much like a view or temp table that you can join against.
DECLARE @sDelimitedString NVARCHAR(4000)
SET @sDelimitedString = 'A,B,C'
SELECT ID, Value FROM dbo.tvfParseDelimitedString(@sDelimitedString,',')
SELECT ItemNumber, Item FROM dbo.tvfDelimitedSplit8K(@sDelimitedString,',')
Here's another very simple example where the delimited string is split and used to join with another table:
DECLARE @sDelimitedString NVARCHAR(4000)
SET @sDelimitedString = 'A,B,C'
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT NOT NULL,
[Name] NVARCHAR(50) NULL,
[Item] NVARCHAR(50) NULL,
PRIMARY KEY (ID),
UNIQUE (ID))
--test data
INSERT INTO #TempTable
SELECT
1 AS ID
,'Apple' AS Name
,'A' AS Item
UNION
SELECT
2 AS ID
,'Banana' AS Name
,'B' AS Item
UNION
SELECT
3 AS ID
,'Carrot' AS Name
,'C' AS Item
SELECT
t.ID
,Name
,Item
,Value
FROM
#TempTable AS t
CROSS APPLY
dbo.tvfParseDelimitedString(@sDelimitedString,',') AS pds
WHERE
t.Item = pds.Value
November 9, 2012 at 1:29 pm
This is a great method I have used a number of times. It uses a function and cross apply.
http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply