Simple LIKE question - 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)

Simple LIKE question

Author

Topic

notmyrealname

98 Posts

Posted - 2010-10-20 : 10:04:10

This must be simple but i can't come up with the solution.

How do you use the LIKE clause to determine if a nvarchar value is between 1 and say 15? I know i can say LIKE '[1-9]' if i want values from 1 to 9 but how do i handle the possible additional characters?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 10:51:28

Just an idea...
where column like '%[^1-9][1-9][^0-9]%' or column like '%[1][0-5]%'

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-20 : 12:29:27

Is there really a reason to use LIKE?

select * from xyz
where isnumeric(col) = 1
and convert(decimal(10,0),col) between 1 and 15

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 16:14:50

I assumed that there are other characters in the column too - like 'ABC12XYZ'

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-20 : 18:11:14

Not sure what you mean by "additional characters", so maybe some sample data and expected output would help. but, I borrowed from Webfred and came up with this:

DECLARE @Foo TABLE (Val VARCHAR(10))

INSERT @Foo VALUES
('0'),
('ABC'),
('16'),
('9'),
('10'),
('13')


SELECT *
FROM @Foo
WHERE 
    Val LIKE '[1-9]' 
    OR Val LIKE '[1][0-5]'

notmyrealname

98 Posts

Posted - 2010-10-22 : 13:02:48

quote:
Originally posted by Vinnie881

Is there really a reason to use LIKE?

Yes. It needs to be a simple SQL query that contains only conditional operators and no functions. The SQL string will be passed to the Select method of and ADO.Net DataTable object. So i need a simple query.

quote:
Originally posted by webfred

I assumed that there are other characters in the column too - like 'ABC12XYZ'

No. There aren't any other characters. There are simply integer values stored as strings (out of my control!). I just want to select 1 thru 15 with a LIKE operator.

quote:
Originally posted by webfred

Just an idea...
where column like '%[^1-9][1-9][^0-9]%' or column like '%[1][0-5]%'

This will work. I do have a filter builder within my application that easily creates complex SQL queries but i just wasn't sure if a single LIKE condition could work.

Thanks for the help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-23 : 05:50:30

welcome

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-24 : 15:32:02

If the values are all integers, there is no reason for a like operator.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

notmyrealname

98 Posts

Posted - 2010-10-25 : 11:54:23

quote:
Originally posted by Vinnie881

If the values are all integers, there is no reason for a like operator.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Unforetunately they are integers but they are stored in a text column. This is why i needed the like. I liked the Convert idea but i just can't use it in my ado.net datatable select method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources