character check - 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

Transact-SQL (2000)

character check

Author

Topic

sameerv
Starting Member

29 Posts

Posted - 2002-10-16 : 10:57:19

HI,

I have a varchar variable containing a comma delimited string.
How will I write a query to return all rows in which a column value contains even 1 character which does not exist in the variable.

e.g.
set @var='a,b,v,@,8,*'

table BankMaster has 2 fields : Bank Code and Bank Name

I need to return all rows of Bank Master in which the value in the Bank Name column has even 1 character which is not a or b or v or @ or 8 or * .

PS: The variable need not be comma delimited i.e. it can be
@var='abv@8*' but the check has to be against each character and not the entire string.
Also, the string will have a constant value which will be fixed statically.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-16 : 11:02:06

Rather than a variable, you should put your characters in a table ...


select bankcode, bankname
from bankmaster b
where exists ( select 1
               from var v
               where charindex(v.word,b.bankname) > 0 )

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-16 : 11:05:57

If you take the commas out of the string then this should work too:

SELECT * FROM BankMaster
WHERE BankCode NOT LIKE '%[abv@8*]%'
AND BankName NOT LIKE '%[abv@8*]%'

sameerv
Starting Member

29 Posts

Posted - 2002-10-17 : 08:48:39

quote:

If you take the commas out of the string then this should work too:

SELECT * FROM BankMaster
WHERE BankCode NOT LIKE '%[abv@8*]%'
AND BankName NOT LIKE '%[abv@8*]%'

Hi robvolk,

The above query does not work for my requirement.
(Ps: We can ignore the BankName field and only work with the BankCode)
e.g.
Suppose I have a record having a value 'ab=' in the BankCode field.
The query I require should return me this record since it contains a character (=) which does not exist in the check string (abv@8*).

On the contrary, your query does not return this record since it contains a character (a) which exists in the check string (abv@8*).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-17 : 08:50:56

Ooooooooooooooooooooooooooooooooooops.....

How about this:

SELECT * FROM BankMaster
WHERE BankCode LIKE '%[^abv@8*]%'

sameerv
Starting Member

29 Posts

Posted - 2002-10-17 : 09:10:40

quote:

Ooooooooooooooooooooooooooooooooooops.....

How about this:

SELECT * FROM BankMaster
WHERE BankCode LIKE '%[^abv@8*]%'

Purrrfect !
And Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources