substring a string at particular location..

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)

substring a string at particular location..

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-02-25 : 12:40:38

Hi

i have a field called customer_token which has data in the following format

5 NM 5.00 7 316 9 131 15 64

7 5053 12 2 18

5 NM 5.00 7 914 9 131 15 64

7 5055 12 2 18

etc...

i need to get only the immediate integers after the number 7 i.e 316 ,5053,914,5055.

how can i do that...

thanks in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-25 : 12:53:01

this works ?/

select left(ltrim(right(a,len(a)-charindex('7',a))),charindex(' ',ltrim(right(a,len(a)-charindex('7',a))))) from
yourtable

replace a with your column name.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-02-25 : 17:01:37

hi saket,

thanks for ur response..

i need only the three or four integrs after the 7 not the entire string.

please check my intial request. those numbers are my customer numbers.

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-25 : 17:12:12

akpaga, did you try his solution? It appears what he did was look for the first 7, then look for the next empty space, and take whatever characters lie between the 7 and the empty space. That should do it, no?

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-02-25 : 17:40:41

it appears so but still even the other integers in the string are being shown up ,for instance example customer_token:

7 129 18 To purchase new item.

when i use the query by saket result shows up in this way:
129 18 To

thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 17:48:49

no..not possible...

whatever sakets has provided shud work fine..and it does...

see below.

select left(ltrim(right('7 129 18' ,len('7 129 18' )-charindex('7','7 129 18' ))),charindex(' ',ltrim(right('7 129 18' ,len('7 129 18' )-charindex('7','7 129 18' )))))

it gave me 129 as the result.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 17:52:10

It should give you right output.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-02-25 : 18:12:51

Hi friends

when i try the query in the way vijay suggested it works but when i
mention the filed name as customer_token it shows the result in the manner i mentioned.

in the select query i have other table joins to the table containing the token field . do u think thats gonna make any difference.

thank you so much.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 18:16:17

Post your query.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-02-26 : 10:54:39

hi

may be the space after 7 is an ascii tab .

that is the reason it may be doing the weird thing when i use the field name.
can you help now?

thanks

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-02-27 : 10:26:36

i have found out that space in examples i have shown is actually
a tab char(10) i guess. Can u provide me the query now.

this how the example looks when i copy the string from database and paste it here.
ex: 7 815
64 07:23:25 0

appreciate your help .thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-27 : 11:16:20

Have you tried using tab char(10) instead of single space for charindex in the same query.

select left(ltrim(right(a,len(a)-charindex('7',a))),charindex(' ',ltrim(right(a,len(a)-charindex('7',a))))) from
yourtable

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-03-05 : 17:06:30

thanks vijay

it worked but i have another problem.

what if there is a 7 somewhere before the actual 7 i am looking for

like in this example:5 NM 576456 7 914 9 131 15 64

the query is then returning the result as 6456 instead of 914.

how can i avoid this .
thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 17:25:40

Ok...then change the CHARINDEX for the '7' to include 7 and the tab of char(10) or whatever....like this..

select left(ltrim(right(a,len(a)-charindex('7 ',a))),charindex(' ',ltrim(right(a,len(a)-charindex('7 ',a))))) from
yourtable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources