How to use 'LIKE' in sql - 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)

How to use 'LIKE' in sql

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-14 : 11:35:13

Hi..

I am trying to get only tables start with the name 'RDB_PAS......'and i also dont want tables ending with name '...DATA_TO_LOAD'

i used
SELECT name FROM sys.tables
WHERE NAME NOT LIKE '%DATA_TO_LOAD'

when i run this script i am able to get rid of tablenames ending withDATA_TO_LOAD but at the same time unwanted tablenames which are not starting with the name 'RDB_PAS_.....' also getting.

could you pls tell how to get rid of these unwanted table names.

Looking for only tablename start with'RDB_PAS...'and also dont want table names ending with 'RDB_PAS_....._DATA_TO_LOAD'
Satya

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-04-14 : 11:42:26

You could try this:



SELECT name FROM sys.tables
WHERE (NAME not like '%DATA_TO_LOAD'
or name not like 'RDB_PAS%')

Laura

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-14 : 11:47:18

Hi..laura
thnax for ur reply,

i tested ur script but still iam getting unwanted data which tablename not starting with the name 'RDB_PAS...'
ex:TEST_RDB_PAS_AUDIT
LINK_OUTPUT_DATA
GRADE_TEST_DATA

....ect...there are 45 records which are not starting with 'RDB_PAS..' so i want to get rid of these 45 records.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-14 : 11:58:54

Thanx laura,

i used the script other way it works

SELECT name FROM sys.tables
WHERE (NAME like '%RDB_PAS%'
and name not like '%DATA_TO_LOAD')

Satya

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-15 : 04:04:23

SELECT name FROM sys.tables
WHERE (NAME like 'RDB_PAS%'
and name not like 'RDB_PAS%DATA_TO_LOAD')

is more precise (and efficient) for your original stated requirement.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 04:10:50

quote:
Originally posted by mayerl

You could try this:

SELECT name FROM sys.tables
WHERE (NAME not like '%DATA_TO_LOAD'
or name not like 'RDB_PAS%')

Laura

NOT OR NOT condition on same column is always true!

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 04:28:01

HI..
THANX FOR UR HELP..

i changed my script now.when i run the script i didnt find any diff between my old and new script even though u said the new one more afficient so i replaced with your script andrew.

thanx web for telling me the difference.

Satya

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-15 : 07:57:16

Having WHERE clauses with a leading "%" will invoke a table scan - every record MUST be inspected to see if it qualifies. Having some leading characters before the "%" will give the query SOME chance of using an index (to eliminate unnecessary data reads) that may exist on that column.

Any "measured" performance improvement will depend on the number of records in your table.

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 08:21:09

thanx andrew,

i got some usefull information.

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources