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


substring/charindex


substring/charindex

Author
Message
rightontarget
rightontarget
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 475
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 1118
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
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 475
what's 99 for?
David Webb-CDS
David Webb-CDS
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3846 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
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 475
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 1118
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
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 475
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3846 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 1118
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
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 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