Sql query using LIKE - 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)

Sql query using LIKE

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-16 : 10:35:44

Hi..
the same problem yesterday i working with but today i got new query to work with...

i have 500 records in the database, form this i want only names start wit 'PAS_....' and i want to get rid of names containg '...CONTROL_CHAR_COUNT' at the end of the table name and '...FIX_NULL...' names at the middle.

this is the script i am using
WHERE
([name] LIKE 'PAS%'
and [name] not like 'PAS%CONTROL_CHAR_COUNT%FIX_NULL')

after executing i am getting all the table name start with 'PAS' up to this ok but i am getting names with CONTROL_CHAR_COUNT &FIX_NULL as well.

could you pls guide me in this query.

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 10:39:26

WHERE
([name] LIKE 'PAS%'
and [name] not like '~~PAS~~%CONTROL_CHAR_COUNT%FIX_NULL')

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-16 : 11:01:06

Hi..fred,

still i am getting the unwanted records

ex:PAS_ALERTS_CODES_CONTROL_CHAR_COUNT
PAS_FIX_NULL_EAS_DETAILS_GEC

this type of records i dont want to pull into my final table.

pls go the script for reference.

INSERT INTO #logDetails ([TABLE_NAME], [ROW_COUNT], [RUN_DATE])
SELECT
' + QUOTENAME([name], '''') + '
, COUNT(*)
, GETDATE()
FROM
' + QUOTENAME([name])
FROM
sys.tables
WHERE
([name] LIKE 'PAS%'
and [name] not like '%CONTROL_CHAR_COUNT%FIX_NULL')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-16 : 11:19:18

Not sure if this works or not. Would help to have sampel data and ddl to execute against:

WHERE
(
	[name] LIKE 'PAS%'
	and [name] not like 'PAS%CONTROL_CHAR_COUNT%'
	and [name] not like 'PAS%FIX_NULL%'
)

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-16 : 11:31:41

Hi

Still same unwanted data..

Satya

Kristen
Test

22859 Posts

Posted - 2010-04-16 : 13:17:22

"i tried with ur script"

"Text Speak" is not acceptable on this forum. Please edit your post (using the

button) to use correct English as far as is possible so that Search Engines index this thread in a way that other people with a similar problem will find this thread and hopefully the solution to their problem too, and also so that people who's native language is not English stand a fighting chance of understanding your post.

Please remember that SQL Team is a forum where people give up their time for free to help others, and also to build a best-of-breed resource "for the greater good"; because people are providing their time for free to help you the least you can do is phrase your question carefully, and take time to check spelling and formatting.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-16 : 13:45:59

Works just fine for me.

DECLARE @Foo TABLE ([Name] VARCHAR(50))

INSERT @Foo
SELECT 'PAS_ALERTS_CODES_CONTROL_CHAR_COUNT'
UNION ALL SELECT 'PAS_FIX_NULL_EAS_DETAILS_GEC'
UNION ALL SELECT 'PAS_FOO'
UNION ALL SELECT 'PAS_BAR'
UNION ALL SELECT 'FOO'
UNION ALL SELECT 'BAR'

SELECT *
FROM @Foo
WHERE
(
	[name] LIKE 'PAS%'
	and [name] not like 'PAS%CONTROL_CHAR_COUNT%'
	and [name] not like 'PAS%FIX_NULL%'
)

It would good if you could provide us sample data and expected output (see the link I posted previously). Or perhaps your definition of the problem is not correct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources