Play with string --- Need help

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 2005 Forums

Transact-SQL (2005)

Play with string --- Need help

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-05 : 10:46:57

I'm really need help.

My table as follows,
tLocation
RID | Posi
------------------------------
_r00000024 | _t00000107
_r00000024 | _t00000041
_r00000024 | _t00000038
_r00000006 | _t00000034
_r00000006 | _t00000165
_r00000007 | _t00000042
_r00000007 | _t00000255
_r00000007 | _t00001123
_r00000007 | _t00000089

1st, i dont know how to trim Posi from varchar to int. So that, the result as follows,
RID | Posi
------------------------------
_r00000024 | 107
_r00000024 | 41
_r00000024 | 38
_r00000006 | 34
_r00000006 | 165
_r00000007 | 42
_r00000007 | 255
_r00000007 | 1123
_r00000007 | 89

after result as above, i dont know how to sort Posi (like ranking). so that, my final result as follows,
RID | Posi
------------------------------
_r00000024 | 3
_r00000024 | 2
_r00000024 | 1
_r00000006 | 1
_r00000006 | 2
_r00000007 | 1
_r00000007 | 3
_r00000007 | 4
_r00000007 | 2

please help

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-05 : 10:58:55

This doesn't make much sense to me...but..

select RID
,cast(right(Posi,(len(Posi) -2)) as int) as Posi
,row_number() over(partition by RID order by cast(right(Posi,(len(Posi) -2)) as int)) as seq
from tLocation

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 11:02:24

Will Posi always be the same length and will numbers start at the same position? (the 1st 0 will always be in the 3rd char?)

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-05 : 11:34:31

quote:
Originally posted by DP978

Will Posi always be the same length and will numbers start at the same position? (the 1st 0 will always be in the 3rd char?)

yes.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-05 : 11:41:06

Did you try the solution posted?

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-05 : 11:53:03

quote:
Originally posted by vijayisonly

Did you try the solution posted?

just try. it's work!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-05 : 12:13:25

Ok. Good

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-05 : 12:16:47

Ya sorry I had tested his Vijay's code and knew it was fine, but had to ensure that there was consistent data in Posi, the function requires uniformity, or else you will get unexpected results.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 01:40:46

If there is no fixed format

select RID
,cast(substring(posi,patindex('%[0-9]%',posi),len(posi)) as int) as Posi
,row_number() over(partition by RID order by cast(substring(posi,patindex('%[0-9]%',posi),len(posi)) as int) as seq
from tLocation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources