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

Transact-SQL (2005)

string function

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-24 : 10:18:51

Hi all,

I want to use the string function inthe where clause to check

say for eg.

I have col values as krahul,akumar,spavan_dk,ssujat_py
I want to equate this values to

'krahul' eliminate k and equate to 'rahul'
'akumar' eliminate a and equate to 'kumar'
'spavan_dk' eliminate s and _dk and equate to 'pavan'
'ssujat_py' elimniate s and _py and equate to 'sujat'

take it as col A tab1 contains krahul,akumar,spavan_dk,ssujat_py

and col B of tab2 rahul,kumar,pavan,sujat

I am trying with char index and patindex plz help.

regards,
aak

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-24 : 10:44:27

what do you have so far?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-24 : 10:49:10

I cannot copy past.. it is on remote system..
i have used a combination of left, right with charindex
it is working fine for the col with _ with failing for with out _ (underscore)

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-24 : 11:01:03

substring(substring(colA,1,charindex('_',cola)-1),2,len(substring(cola,1,charindex('_',cola)-1)))

the above works fine if cola = spavan_dk,ssujat_py
however when cola=krahul or akumar this fails..

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-24 : 11:27:04

Declare @Cola varchar(50)
Set @cola = 'ssujat_py'

select case when charindex('_', @cola) > 0 Then substring(substring(@cola,1,charindex('_',@cola)-1),2,len(substring(@cola,1,charindex('_',@cola)-1)))
else Right(@cola,len(@cola)-1)
End

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-24 : 11:53:48

Thanks Dinkar..
i used case stm and its working..
your inputs were valuable

regards,
aak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources