Query with pattern matching - 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

General SQL Server Forums

New to SQL Server Programming

Query with pattern matching

Author

Topic

Praveen_ak
Starting Member

3 Posts

Posted - 2010-04-22 : 07:35:00

Could anybody help me with the following query:
Find all the records in the table which have only 9`s as value in one of the column.
Eg: Table T1 has a column called Amount. I need to find the rows which have only 9`s in the column amount like 9 OR 99 OR 999 OR 9999 and so on... Required urgently. Please help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 07:43:50

What is the data type of column amount?

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

Praveen_ak
Starting Member

3 Posts

Posted - 2010-04-22 : 07:55:25

@Webfred: column data type is string(character). Thank You.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-04-22 : 07:57:51

declare @table table (col1 varchar(5))
insert into @table
select '9' union all
select '7' union all
select '999' union all
select '7997'

select * from @table where col1 not like '%[0-8]%'

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 08:36:04

quote:
Originally posted by jimf

declare @table table (col1 varchar(5))
insert into @table
select '9' union all
select '7' union all
select '999' union all
select '7997'

select * from @table where col1 not like '%[0-8]%'

Jim

Everyday I learn something that somebody else already knew

this will return everything except 0-8 including ones containing alphabets

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 08:42:38

may be this


select * from @table where col1 not like '%[0-8]%'
and col1 not like '%[A-Za-z$]%'
and ISNUMERIC(col1)=1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-22 : 08:45:37

where col1 not like '%[^9]%'

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 08:54:50

quote:
Originally posted by madhivanan

where col1 not like '%[^9]%'

Madhivanan

Failing to plan is Planning to fail

That's cool

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 09:03:03

quote:
Originally posted by madhivanan

where col1 not like '%[^9]%'

Madhivanan

Failing to plan is Planning to fail

wow thts good one

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-22 : 09:18:59

Thanks

Madhivanan

Failing to plan is Planning to fail

Praveen_ak
Starting Member

3 Posts

Posted - 2010-04-23 : 01:25:38

Guys Thank you so much for the quick responses. My problem got solved. I really did not expect such quick help!
Thank you to the forum too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:21:09

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources