getting part o 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

SQL Server 2000 Forums

SQL Server Development (2000)

getting part o a string

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-01-30 : 10:08:34

hi

i have a field which is a token string
the data is in following formati
7*809*......so on
7*4567*.........so on

809 and 4567 are customer ids . So my question is i want to show just
those numbers but sometimes the customer number is 3 integers and sometimes 4 .
How can i get that.

Thaks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 10:21:47

are you using sql 2005?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-30 : 10:26:27

Something like this in SQL 2000.


declare @tab as table(InputString varchar(50))

insert into @tab
select '7*809*erfujd*dckvchd'
union select '7*4567*73484*edfhdjfh'

select convert(int,SUBSTRING(InputString,3,PATINDEX('%*%',SUBSTRING(InputString,3,LEN(InputString)))-1)) from @tab

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-01-30 : 10:44:46

Hi Rick but its not just the just examples i gave ...there are countless tokenstrings

quote:

Originally posted by RickD

Something like this in SQL 2000.


declare @tab as table(InputString varchar(50))

insert into @tab
select '7*809*erfujd*dckvchd'
union select '7*4567*73484*edfhdjfh'

select convert(int,SUBSTRING(InputString,3,PATINDEX('%*%',SUBSTRING(InputString,3,LEN(InputString)))-1)) from @tab

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-30 : 10:47:14

So change InputString to your field and @tab to your table and work with what I have given you, I can only work with the samples you provide.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 11:59:58

is it that you'll have multiple customerids coming in each column value?

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-01-30 : 17:55:07

quote:
Originally posted by visakh16

is it that you'll have multiple customerids coming in each column value?

the table design is like this

customer id, customer_token
1 7*345*hdfhdkkdfjkd
2 7*3458*494jekrjkejrker
3 7*123*HFHDKHFDFHKDF

SO ON...

WHAT IS NEED IS FROM THE CUSTOMER_TOKEN FIELD
NUMBERS THAT ARE AFTER 7. I.E 345 ,3458,123 SO ON

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-31 : 00:15:14

try this

declare @temp table ( customerid int, customer_token varchar(8000))
insert into @temp
select 1, '7*345*hdfhdkkdfjkd' union all
select 2, '7*3458*494jekrjkejrker' union all
select 3,'7*123*HFHDKHFDFHKDF'

select
customerid,
substring(substring(customer_token,charindex('*',customer_token)+1,len(customer_token)),1,charindex('*',substring(customer_token,charindex('*',customer_token)+1,len(customer_token)))-1) as customer_token
from @temp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-31 : 01:06:30

Search for function fnParseStrind and/or fnParseList here on SQLTeam.

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

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-31 : 01:14:43

[code]
declare @temp table ( customerid int, customer_token varchar(8000))
insert into @temp
select 1, '7*345*hdfhdkkdfjkd' union all
select 2, '7*3458*494jekrjkejrker' union all
select 3,'7*123*HFHDKHFDFHKDF' union all
select 4, '*12121212121*asdfasdfdasf'

select substring(customer_token,charindex('*',customer_token)+1,charindex('*',customer_token,charindex('*',customer_token)+1)-(charindex('*',customer_token)+1)) from @temp

Jai Krishna

[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-31 : 01:50:56

Try this also


declare @temp table ( customerid int, customer_token varchar(8000))
insert into @temp
select 1, '7*345*hdfhdkkdfjkd' union all
select 2, '7*3458*494jekrjkejrker' union all
select 3,'7*123*HFHDKHFDFHKDF' 

declare @temp1 table ( customerid int,a varchar(32))
insert into @temp1
select customerid, substring(customer_token,charindex('*',customer_token)+1,len(customer_token)) from @temp
select customerid,substring(a,1,charindex('*',a)-1) as token from @temp1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-31 : 02:10:50

Try this also

declare @temp table ( customerid int, customer_token varchar(8000))
insert into @temp
select 1, '7*345*hdfhdkkdfjkd' union all
select 2, '7*3458*494jekrjkejrker' union all
select 3,'7*657*HFHDKHFDFHKDF' union all
select 4,'7*768*HFHDKHFDFHKDF' union all
select 5,'7*678*HFHDKHFDFHKDF'

select parsename(replace(customer_token,'*','.'),2) from @temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:48:13

[code]SELECT t.customerid,f.Val
FROM Table t
CROSS APPLY dbo.ParseValues(t.customer_token ,'*')f
WHERE f.ID=2
[/code]

parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources