Field LIKE number,number,number

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

Field LIKE number,number,number

Author

Topic

Tall Dude
Starting Member

2 Posts

Posted - 2006-11-26 : 02:29:02

I am testing something in Visual Basic
that talks to a database and I want to
filter results by -> field1 like "###".
However, that 'like' and '#' is VB syntax.
How do you say that in SQL?

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2006-11-26 : 02:35:29

Here an example from a user ID parser,


WHEN Strings LIKE '[0-9][0-9][0-9]%' THEN SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
WHEN  Strings LIKE '-|[0-9][0-9][0-9]%' THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE '-|[a-z]%' THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE 'Account Unlocked.  |%' THEN SUBSTRING(Strings,21,charindex('|',Strings,21)-21)
ELSE SUBSTRING(Strings,1,charindex('|',Strings,1)-1) END,

Thank You,

John Fuhrman
http://www.titangs.com

Kristen
Test

22859 Posts

Posted - 2006-11-26 : 02:50:54

If you are looking for values which are exactly 3 digits then:

If the column is a String datatype:

WHERE MyColumn LIKE '[0-9][0-9][0-9]'

(assuming that you don't allow "." or "-")

If the column is a numeric datatype, and again assuming just digits, then:

WHERE MyColumn >= 100 AND MyColumn <= 999

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-26 : 04:32:40

Is that not a contradiction?
mycolumn like [0-9][0-9][0-9]

and

Mycolumn >= 100 and mycolumn <= 999

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-26 : 12:10:17

I don't think so, you can't represent 001 as an INT, but you can as a string.

Might not be what Tall Dude wants, but I expect it is what "###" means in VB

Kristen

Tall Dude
Starting Member

2 Posts

Posted - 2006-11-26 : 12:30:30

Thank you all for your replys.
The field is indeed a string.
I have tried the [0-9] and others
suggestions from the replies here. Nothing works.
Maybe I am wrong in what VB wants
for this filter.
The '###' syntax does not error out
in VB, but does not work either.
So, not to tie up this SQL forum any
further. If any of you have Visual Studio
or VB express, please look at the original
thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=951677&SiteID=1.

Hopefully if I get a chance to work with databases
more in the future, I will be back to this forum.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-26 : 12:52:28

If you want to search for strings starting with 3 digits and followed by anything, then:

Where column like '[0-9][0-9][0-9]%'

But if you want to show only those records which has digits in the column like in the link which you posted, then:

Where column not like '%[a-zA-Z]%'

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-11-26 : 13:04:36

"Where column not like '%[a-zA-Z]%'"

Or if it could be any non-numeric-digit, rather than specifically alphabetic characters, then:

Where column not like '%[^0-9]%' AND column <> '' AND column IS NOT NULL

which will find any column that does not contain just numeric digits.

Note that this is SQL, not a VB filter.


DECLARE @MyTable TABLE
(
	CardNumber	varchar(5) NULL
)

INSERT INTO @MyTable(CardNumber)
SELECT '001' UNION ALL
SELECT '345' UNION ALL
SELECT '732' UNION ALL

SELECT '001WB' UNION ALL
SELECT 'WB1' UNION ALL
SELECT 'SET' UNION ALL

SELECT '' UNION ALL
SELECT NULL UNION ALL
SELECT '1234' UNION ALL
SELECT '1' UNION ALL
SELECT '12'

SELECT	[3 digits] = CardNumber
FROM	@MyTable
WHERE	CardNumber LIKE '[0-9][0-9][0-9]'

SELECT	[Not 3 digits] = CardNumber
FROM	@MyTable
WHERE	CardNumber NOT LIKE '[0-9][0-9][0-9]' OR CardNumber IS NULL

SELECT	[Only all digits] = CardNumber
FROM	@MyTable
WHERE	CardNumber NOT LIKE '%[^0-9]%' AND CardNumber <> '' AND CardNumber IS NOT NULL

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources