Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Like expression help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
550 Posts

Posted - 11/07/2012 :  07:23:47  Show Profile  Reply with Quote
Hello there

I want to use the like within a where clause.

All iam using is where colomn like '%_%'

but i need to make that up of char numbers eg

select convert(varchar(5),char(39)+char(37)+char(95)+char(37)+char(39))

this will not work. is that way i can create the '%_%' sting so the query will except?

Regards

Rob

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 11/07/2012 :  07:43:54  Show Profile  Reply with Quote
do u mean value which is having _ (symbol) ??
Eg: a_bc
    _abc
    abc_


For the above requirement,

WHERE columnName LIKE '%@_%' ESCAPE '@'
------------------------------

For %anycharacter% format,
Eg: %b%

WHERE columnName LIKE '@%_@%' ESCAPE '@'


--
Chandu

Edited by - bandi on 11/07/2012 07:46:25
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/07/2012 :  07:44:23  Show Profile  Reply with Quote
Not sure what you mean, the following works as I would expect returning 1, 1, and 0, respectively
DECLARE @x VARCHAR(32) = 'xyz'+char(39)+char(37)+char(95)+char(37)+char(39)+'abc';

SELECT CASE -- returns 1
	WHEN @x LIKE '%'+char(39)+char(37)+char(95)+char(37)+char(39)+'%' THEN 1 ELSE 0 END;
	
SELECT CASE -- returns 1
	WHEN @x LIKE 'xyz'+char(39)+char(37)+'%' THEN 1 ELSE 0 END;
	
SELECT CASE-- returns 0
	WHEN @x LIKE 'xyzabc%' THEN 1 ELSE 0 END;
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000