Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

substring/charindex Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 3:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
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,
Post #1382190
Posted Wednesday, November 7, 2012 3:26 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:08 PM
Points: 271, Visits: 1,019
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 hardest
Post #1382195
Posted Wednesday, November 7, 2012 3:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
what's 99 for?
Post #1382202
Posted Wednesday, November 7, 2012 3:36 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 901, Visits: 7,185
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
Post #1382205
Posted Wednesday, November 7, 2012 3:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
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.
Post #1382209
Posted Wednesday, November 7, 2012 3:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:08 PM
Points: 271, Visits: 1,019
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 hardest
Post #1382210
Posted Wednesday, November 7, 2012 4:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
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?
Post #1382211
Posted Wednesday, November 7, 2012 4:45 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 901, Visits: 7,185
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
Post #1382216
Posted Thursday, November 8, 2012 3:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:08 PM
Points: 271, Visits: 1,019
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 hardest
Post #1382354
Posted Thursday, November 8, 2012 3:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, 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)


Post #1382356
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse