Extracting a substring, unfixed 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 2005 Forums

Transact-SQL (2005)

Extracting a substring, unfixed location

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-03-17 : 14:39:57

Hi,
I have a very simple script.

select * from A join B
ON A.CODE=B.CD
WHERE B.CD = 1

The question is that A.CODE = 'E_CASH' OR 'Fr_CASH'
But B.CD = 'CASH'
I don't know how to extract the substring 'CASH'.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 14:43:50

try
on a.code like '%'+b.cd+'%'

No, you're never too old to Yak'n'Roll if you're too young to die.

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-17 : 14:44:00

[code]
Select * from A INNER JOIN B
ON RIGHT(A.CODE, 4) = B.CD
WHERE B.CD=1
[/code]

This will only work if 'CASH' is always the last 4 characters of A.CODE.

BTW, why is "B.CD=1"? If B.CD='Cash'?

How to ask good questions that get answers:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Ray Dai

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-03-17 : 14:51:05

Sorry, it should be

select * from A inner join B
ON A.CODE=B.CD
WHERE B.DESCR = 1

btw
ON RIGHT(A.CODE, 4) = B.CD
is not good for me.

Because there are other cases which like
A.CODE = 'E_Credit Card' OR 'Fr_Credit Card'
and B.CD = 'Credit Card' etc.
Anyway, the substrings are same after '_'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 14:52:21

did you try my approach?

No, you're never too old to Yak'n'Roll if you're too young to die.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-03-17 : 14:58:42

Yes. Great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources