Extracting part of a string - SQL Server Forums

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

Extracting part of a string

Author

Topic

kofseattle
Starting Member

8 Posts

Posted - 2006-08-22 : 16:11:38

Hello,

I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example:

"the text looks like this but has two numbers 55555/66666 within the string"

I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-08-22 : 16:48:04

hello,
welcome to SQLTeam.

Where would you like to extract the string ? in ASP or MS SQL.

In ASP, use the split function


dim kofseattle,kofseattle2
kofseattle = "your_string"
kofseattle2 = split(kofseattle,"/")

and then use your arrays to get the values.

in MS SQL use the substring function and charindex to find the locations.

hope this helps
Afrika

kofseattle
Starting Member

8 Posts

Posted - 2006-08-22 : 16:53:44

Would that not just split the sentence? I need to find the "/" and then get the five numbers before and then the five numbers after, nothing more. When complete I need the two numbers only. The rest of the sentence is irrelevant. Sorry, remember, I am a beginner. I may just not understand? I hope what I am asking makes sense?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-22 : 17:23:47

You can use the CHARINDEX function to locate the / within the string, and use the result of the CHARINDEX in the SUBSTRING function to get the numbers before or after the /.

CODO ERGO SUM

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-23 : 07:10:01

kofseattle,

Here's something to get you going in the right direction...

--data
declare @t table (v varchar(100))
insert @t
          select 'the text looks like this but has two numbers 55555/66666 within the string'
union all select 'here is some more text with numbers 3232/98 within the string'
union all select '111/222 sdfsdfj'
union all select 'dsfdfsfd 333/444'
union all select '555/666'
union all select ' 777/888 '

--calculation
select v, reverse(left(w, charindex(' ', w + ' ') - 1)) as y, left(x, charindex(' ', x + ' ') - 1) as z
from (
select *, reverse(left(v, charindex('/', v)-1)) as w, substring(v, charindex('/', v)+1, 100) as x from @t) as a

/*results
v                                                                           y      z     
--------------------------------------------------------------------------- ------ ------
the text looks like this but has two numbers 55555/66666 within the string  55555  66666
here is some more text with numbers 3232/98 within the string               3232   98
111/222 sdfsdfj                                                             111    222
dsfdfsfd 333/444                                                            333    444
555/666                                                                     555    666
 777/888                                                                    777    888
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-23 : 09:36:32

quote:
Within this text are two five digit numbers seperated by a forward slash (/).

What if he only wants the 5 digit pairs??


declare @t table (v varchar(100))
insert @t
          select 'the text looks like this but has two numbers 55555/66666 within the string'
union all select 'here is some more text with numbers 3232/98 within the string'
union all select '11111/22222 sdfsdfj'
union all select 'dsfdfsfd 33333/44444'
union all select '55555/66666'
union all select ' 77777/88888 '


Declare @pattern varchar(100)
Set @pattern = '%[0-9][0-9][0-9][0-9][0-9]/[0-9][0-9][0-9][0-9][0-9]%'

Select 
	v,
	case when patindex(@pattern,v) > 0 then
		substring(v,patindex(@pattern,v),5) 
		else null end,
	case when patindex(@pattern,v) > 0 then
		substring(v,patindex(@pattern,v)+6,5) 
		else null end
From @t

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources