Splitting a string that contains delimiters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Splitting a string that contains delimiters

Author

Topic

matpj
Starting Member

8 Posts

Posted - 2009-01-09 : 05:14:16

Hi all,

I have a field that contains a string with possibly up to 4 delimeters.
e.g.
18.0.1.1
15.4.3

what I need to be able to do is split this in my SELECT statement into 2 fields containing the first 2 elements.

Can anybody help me acheive this.
I have tried using substring and charindex but can only seem to use this to get the first element successfully.

for the examples above I would like to see values
18 and 0 in seperate columns in my SQL results
and 15 and 4 in the other case.

thanks in advance,
Matt

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 05:15:32

use parsename function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-09 : 05:15:37

See this site for function named fnParseString.

E 12°55'05.63"
N 56°04'39.26"

matpj
Starting Member

8 Posts

Posted - 2009-01-09 : 05:47:06

Hi, thanks for that.
I have created the function and tried using it but I get interesting results.

my code is as follows:

Select 

versionnumber, 
dbo.fnParseString(3, '.', versionnumber) as majorrelease, dbo.fnParseString(2, '.', versionnumber) as minorrelease

from dbo.DSTi_Reporting_ReplicationData_Calls

now, if I have a versionnumber field like '15.13.0'

the code above returns
versionnumber, majorrelease, minorrelease
15.13.0, 15, 13

which is fine.
however, these are the reuslts I get for different versions:
versionnumber, majorrelease, minorrelease
18.x, NULL, 18
1.2.0.1, 2, 0
2.6.0.2, 6, 0

can anyone tell me why?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 05:47:16

hi ,

try this

declare @temp table ( a varchar(40))
insert into @temp
select '18.0.1.1' union all
select '15.4.3' union all
select '12.3' union all
select '1.2.3.4' union all
select '12.23.45'

select replace(substring(a,1,charindex('.',a)-1),'.','') as col1 ,
replace(substring(a,charindex('.',a)+1,charindex('.',a)-1),'.','') as col2
from @temp
where charindex('.',a) >0

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 05:56:12

hi,

try this

Select

versionnumber,
dbo.fnParseString(4, '.', versionnumber) as majorrelease, dbo.fnParseString(3, '.', versionnumber) as minorrelease

from dbo.DSTi_Reporting_ReplicationData_Calls

matpj
Starting Member

8 Posts

Posted - 2009-01-09 : 06:00:24

Hi raky,
your first response works if the field contains at least 1 delimiter.
Some of the products have versions without.
Is there a way I can check that it does contain a delimiter character first?

as for the second post using the function, that did not work either.
It seems to have different results depending on how many delimeters are in the field.
there are sometimes 2 sometimes 3 and rarely 4...

Its like I need a function to count the number (in both cases)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-09 : 06:02:31

matpj, you can provide negative parameter values for my function to count from left-to-right!
Select

versionnumber,
dbo.fnParseString(-1, '.', versionnumber) as majorrelease, dbo.fnParseString(-2, '.', versionnumber) as minorrelease

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 06:08:55

Hi raky,
your first response works if the field contains at least 1 delimiter.
Some of the products have versions without.
Is there a way I can check that it does contain a delimiter character first?

select versionnumber
from dbo.DSTi_Reporting_ReplicationData_Calls
where charindex('.',versionnumber) = 0

the above query will give those versionnumbers which are not having any delimeter..

matpj
Starting Member

8 Posts

Posted - 2009-01-09 : 07:31:13

Thank you both for your help.
I guess I didn't really understand how the ParseString function worked - but was just playing around with it.
Thanks for clearing that up Peso, it works fine

raky, thank you also for helping me with the count.
I think I will stick with the ParseString function, because in the absense of delmiters it still works and actually puts those version numbers in my selected column (which is how I need it to work)

regards,
Matt

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 08:30:48

quote:
Originally posted by matpj

Thank you both for your help.
I guess I didn't really understand how the ParseString function worked - but was just playing around with it.
Thanks for clearing that up Peso, it works fine

raky, thank you also for helping me with the count.
I think I will stick with the ParseString function, because in the absense of delmiters it still works and actually puts those version numbers in my selected column (which is how I need it to work)

regards,
Matt

Hi the below one work in the absence of delimiter also. Try this one also once

declare @temp table ( a varchar(40))
insert into @temp
select '18.0.1.1' union all
select '15.4.3' union all
select '12.3' union all
select '1.2.3.4' union all
select '12.23.45' union all
select '4'

select replace(substring(a,1,charindex('.',a)-1),'.','') as col1 ,
replace(substring(a,charindex('.',a)+1,charindex('.',a)-1),'.','') as col2
from @temp where charindex('.',a) >0
union all
select a as col1, null as col2
from @temp
where charindex('.',a) = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources