LIKE with int - 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)

LIKE with int

Author

Topic

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 05:48:51

Hi,

I was wondering if it is possible to do something similar to the LIKE operator on strings for numeric datatypes?
Currently I cast the value to varchar and then do the like, but I was wondering if there are any more performant ways to do this.

Practical example:
List of years
user input: 19
wanted output: 1900 -> 1999

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 05:55:16

select * from yourTable WHERE Year(datecolumn)/100 = 19

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 06:01:34

1) Year column is SMALLINT?
2) User input can be "1", "19", "199", "1999"?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 06:06:15

[code]DECLARE @Sample TABLE (YearCol SMALLINT)

INSERT @Sample
SELECT 1899 UNION ALL
SELECT 1901 UNION ALL
SELECT 1910 UNION ALL
SELECT 1911 UNION ALL
SELECT 1990 UNION ALL
SELECT 1991 UNION ALL
SELECT 1999 UNION ALL
SELECT 2000

DECLARE @UserInput VARCHAR(4),
@FromYear SMALLINT,
@ToYear SMALLINT

-- Case 1
SET @UserInput = '1'

SELECT @FromYear = LEFT(@UserInput + '0000', 4),
@ToYear = LEFT(@UserInput + '9999', 4)

SELECT *
FROM @Sample
WHERE YearCol BETWEEN @FromYear AND @ToYear

-- Case 2
SET @UserInput = '19'

SELECT @FromYear = LEFT(@UserInput + '0000', 4),
@ToYear = LEFT(@UserInput + '9999', 4)

SELECT *
FROM @Sample
WHERE YearCol BETWEEN @FromYear AND @ToYear

-- Case 3
SET @UserInput = '199'

SELECT @FromYear = LEFT(@UserInput + '0000', 4),
@ToYear = LEFT(@UserInput + '9999', 4)

SELECT *
FROM @Sample
WHERE YearCol BETWEEN @FromYear AND @ToYear

-- Case 4
SET @UserInput = '1991'

SELECT @FromYear = LEFT(@UserInput + '0000', 4),
@ToYear = LEFT(@UserInput + '9999', 4)

SELECT *
FROM @Sample
WHERE YearCol BETWEEN @FromYear AND @ToYear[/code]

E 12°55'05.25"
N 56°04'39.16"

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 07:53:38

Thanks for all the quick replies.
But I fear I did give a bit of a bad example.
As in the LIKE function, I can say

List of years

user input: 19%
wanted output: 1900 -> 1999

user input: %19
wanted output: 1719, 1819, 1919 etc

user input: %19%
wanted output: 1191, 1192, 1193 etc

I know the example might look a bit farfetched, but it's just to explain the case.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 07:59:50

quote:
Originally posted by BorisCallens

Thanks for all the quick replies.
But I fear I did give a bit of a bad example.
As in the LIKE function, I can say

List of years

user input: 19%
wanted output: 1900 -> 1999

user input: %19
wanted output: 1719, 1819, 1919 etc

user input: %19%
wanted output: 1191, 1192, 1193 etc

I know the example might look a bit farfetched, but it's just to explain the case.

then you should cast to varchar and do it.

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 09:32:13

Point taken ;)
Thanks for the reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources