SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


substring/charindex


substring/charindex

Author
Message
rightontarget
rightontarget
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 468
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,
SGT_squeequal
SGT_squeequal
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 1060
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 is always the hardestw00t
rightontarget
rightontarget
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 468
what's 99 for?
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 8586
And a link to my favorite string splitter. Don't leave home without it...

http://www.sqlservercentral.com/articles/Tally+Table/72993/



And then again, I might be wrong ...
David Webb
rightontarget
rightontarget
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 468
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.
SGT_squeequal
SGT_squeequal
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 1060
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 is always the hardestw00t
rightontarget
rightontarget
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 468
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?
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 8586
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).



And then again, I might be wrong ...
David Webb
SGT_squeequal
SGT_squeequal
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 1060
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 is always the hardestw00t
BriPan
BriPan
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 296
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)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search