Help with small query... - 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)

Help with small query...

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-01-09 : 12:13:58

i have a huge table with 4 columns...

id temporaryName CorrectName Datechanged
1 TEMP abc 1999-01-01
2 abc TEMP abc xyz 2000-01-01
3 abc TEMP abc abc xyz abc 2003-01-01
4 pqr abctemppqr pq abctempqr 2006-01-01

Now i want all the records where there is a Temp in temporaryName column. If i use
SELECT *
FROM tableName
where temporaryName like '%TEMP'%

i am getting the 4th record which i dont want to. Can someone help me with this query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 12:55:59

[code]
SELECT *
FROM tableName
where temporaryName like '% TEMP %'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-01-09 : 13:10:01

i did the same but in the 4th record, i have the temporaryName as abc temppqr and i dont want this in my result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 13:11:12

still it wont come as i've a space following temp as search condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-01-09 : 13:41:33

wow, it worked. What is the difference between '%TEMP%' and '% TEMP %'?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-09 : 14:40:43

The leading and trailing space around TEMP.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:28:56

the leading and training space ensures you only return instances having temp as an individual word in the field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-01-11 : 07:51:31

In real case scenario I wont go for a search pattern like that.I would rather enable a Full Text search for the table.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources