single number search problem - 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)

single number search problem

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-04-10 : 08:15:08

Hi All,

Table structure is Column1 - Column2 - column3 - column4.
E.g. 0-134-00-99, thus the number is 013400 to 013499.
consider the following scenario:
0-1803-0000-9999.
User enters a number as 018030015* --> * means any number starting with this and contained in the range. Hence the above range should be returned. i.e. (0-1803-0000-9999). The issue is to do this search, i will have to check all 1000 NUMBERS because using 'LIKE' will only search for all numbers starting with 018030015.
(SELECT * FROM table1 WHERE Column1+Column2+Column3 LIKE '018030015%') this query won't return the row 0-1803-0000-9999
If i write a cursor to check for 1000 such numbers, i can get the result, but is there a better solution?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 08:20:49

[code]where 18030015 between cast(Column1+Column2+Column3 as int) and
cast(Column1+Column2+Column4 as int)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 08:43:13

There can be a danger to convert to int.

SELECT	*
FROM	YourTableNameHere
WHERE	@Search BETWEEN Col1 + Col2 + Col3 AND Col1 + Col2 + Col4

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

zion99
Posting Yak Master

141 Posts

Posted - 2008-04-10 : 08:50:48

yeah, a conversion error occurs, so i generally use numeric(38,0) as a cast...
Thanks for your help :) but unfortunately, i still dont get the results...
foll are some rows:
0 1803 001503 001503
0 1803 001504 001504
0 1803 001505 001505
0 1803 001506 001506
0 1803 001507 001507
0 1803 001508 001508
0 1803 001509 001509
if we search with

SELECT	*
FROM	@Sample
WHERE	Col1 = SUBSTRING(@Search, 1, LEN(Col1))
	AND Col2 = SUBSTRING(@Search, LEN(Col1) + 1, LEN(Col1) + LEN(Col2) - 1)

then it will retrieve the rows,
but as soon as we add the complete query... it does not return anything.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 08:54:03

Try this

SELECT	*
FROM	YourTableNameHere
WHERE	@Search BETWEEN Col1 + Col2 + Col3 AND Col1 + Col2 + Col4

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 09:00:58

quote:
Originally posted by zion99

... it will retrieve the rows,
but as soon as we add the complete query... it does not return anything.

Post full query.

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 09:03:47

quote:
Originally posted by Peso

There can be a danger to convert to int.
SELECT *
FROM YourTableNameHere
WHERE @Search BETWEEN Col1 + Col2 + Col3 AND Col1 + Col2 + Col4

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

You talking about overflow error?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

zion99
Posting Yak Master

141 Posts

Posted - 2008-04-10 : 09:03:54

i was talking about ur query

DECLARE	@Search VARCHAR(16)
SET	@Search = '018030014'

SELECT	*
FROM	@Sample
WHERE	Col1 = SUBSTRING(@Search, 1, LEN(Col1))
	AND Col2 = SUBSTRING(@Search, LEN(Col1) + 1, LEN(Col1) + LEN(Col2) - 1)
	AND SUBSTRING(@Search, LEN(Col1) + LEN(Col2) + LEN(Col3), LEN(Col4)) BETWEEN Col3 AND Col4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 09:06:34

Use latest version!

SELECT	*
FROM	YourTableNameHere
WHERE	@Search BETWEEN Col1 + Col2 + Col3 AND Col1 + Col2 + Col4

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 09:07:01

quote:
Originally posted by harsh_athalye

You talking about overflow error?

Yes.

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

zion99
Posting Yak Master

141 Posts

Posted - 2008-04-10 : 09:39:15

substring option wont work for the rows.

0 1803 001503 001503
0 1803 001504 001504
0 1803 001505 001505
0 1803 001506 001506
0 1803 001507 001507
0 1803 001508 001508
0 1803 001509 001509

SELECT *
FROM YourTableNameHere
WHERE @Search BETWEEN Col1 + Col2 + Col3 AND Col1 + Col2 + Col4
this option will search for that particular number, i.e. 0015 and NOT 001500 or 001599

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 09:45:08

quote:
Originally posted by Peso

quote:
Originally posted by harsh_athalye

You talking about overflow error?
Yes.

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

I opted for INT conversion since as per OP, range consists of maximum 9 digits, so effectively from 0 - 999999999, which is easily accomodated in INT. Of course, it's my assumption according to information OP gave.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 09:48:02

quote:
Originally posted by zion99

0 1803 001503 001503
0 1803 001504 001504
0 1803 001505 001505
0 1803 001506 001506
0 1803 001507 001507
0 1803 001508 001508
0 1803 001509 001509

SELECT *
FROM YourTableNameHere
WHERE @Search BETWEEN Col1 + Col2 + Col3 AND Col1 + Col2 + Col4
this option will search for that particular number, i.e. 0015 and NOT 001500 or 001599

With the sample data above, what value do @Search have?
And what do you expect to be returned?

Please read and understand this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

You do realize that we have no access to your system?
We can't assist you further without proper sample data and expected output based on the sample data.

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

zion99
Posting Yak Master

141 Posts

Posted - 2008-04-10 : 13:07:07

Table structure is COL1 COL2 COL3 COL4
COL1 is varchar(4)
COL2 is varchar(3)
COL3 and COL4 are varchar(4)

Sample Data:
COL1 COL2 COL3 COL4
100 180 0015 0015
100 180 0016 0016
100 180 0017 0017
100 180 0018 0018
100 180 0019 0019
100 180 0020 0020
100 180 0021 0121
100 180 0131 0140
100 180 0151 0180
100 180 2020 2040
100 180 2050 2070
100 180 2080 2090
100 180 2100 4000

100 180 2100 4000 means number range exists from 100-180-2100 to 100-180-4000

COL3 & COL4 represents the lower bound & upper bound of the number range
total length of the number i.e. LEN(COL1)+LEN(COL2)+LEN(COL3) OR LEN(COL1)+LEN(COL2)+LEN(COL4) will always be 10

consider the following :
@search = '100180017*' (i.e.100-180-017...) should return 100-180-0151-0180
(100180017* can be assumed as any number starting from 100-180-0170 to 100-180-0179 and hence range 100-180-0151-0180 should be the output since @search is contained in this range)

@search = '1001803*' (i.e. 100-180-3...) should return 100-180-2100-4000
(1001803* can be assumed as any number starting from 100-180-3000 to 100-180-3999 and hence range 100-180-2100-4000 should be the output since @search is contained in this range)

please tell me what should be the query to retrieve the above types of results

Note: in the sample data i have shown COL1 with only 3 digit values, but it can be either 3 or 4. In this scenario, i will run the query twice; once assuming COL1 as length 3 and other time COL1 with length as 4 digits.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 14:56:42

This is not what you originally posted.

Try this

SELECT	*
FROM	YourTableNameHere
WHERE	REPLACE(@Search, '*', '') BETWEEN Col1 + Col2 + Col3 AND Col1 + Col2 + Col4

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

zion99
Posting Yak Master

141 Posts

Posted - 2008-04-10 : 16:20:05

Hi Peso, i am sorry about not posting the details properly, i tried with your latest query, but this too doesnt return ne results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:23:55

[code]SELECT *
FROM YourTableNameHere
WHERE REPLACE(@Search, '*', '99999999') >= Col1 + Col2 + Col3
AND REPLACE(@Search, '*', '') <= Col1 + Col2 + Col4[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 16:32:59

[code]-- Prepare sample data
DECLARE @Sample TABLE (Col1 VARCHAR(4), Col2 VARCHAR(3), Col3 VARCHAR(4), Col4 VARCHAR(4))

INSERT @Sample
SELECT '100', '180', '0015', '0015' UNION ALL
SELECT '100', '180', '0016', '0016' UNION ALL
SELECT '100', '180', '0017', '0017' UNION ALL
SELECT '100', '180', '0018', '0018' UNION ALL
SELECT '100', '180', '0019', '0019' UNION ALL
SELECT '100', '180', '0020', '0020' UNION ALL
SELECT '100', '180', '0021', '0121' UNION ALL
SELECT '100', '180', '0131', '0140' UNION ALL
SELECT '100', '180', '0151', '0180' UNION ALL
SELECT '100', '180', '2020', '2040' UNION ALL
SELECT '100', '180', '2050', '2070' UNION ALL
SELECT '100', '180', '2080', '2090' UNION ALL
SELECT '100', '180', '2100', '4000'

-- Initialize search
DECLARE @Search VARCHAR(15)

SET @Search = '100180017*'

-- Show expected output
SELECT Col1,
Col2,
Col3,
Col4
FROM @Sample
WHERE REPLACE(@Search, '*', '9999') >= Col1 + Col2 + Col3
AND REPLACE(@Search, '*', '0000') <= Col1 + Col2 + Col4[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 17:11:23

[code]-- Show expected output
SET @Search = '1001803*'

SELECT Col1,
Col2,
Col3,
Col4
FROM @Sample
WHERE REPLACE(@Search, '*', '9999') >= Col1 + Col2 + Col3
AND REPLACE(@Search, '*', '0000') <= Col1 + Col2 + Col4[/code]

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

zion99
Posting Yak Master

141 Posts

Posted - 2008-04-11 : 03:45:25

Hi Peso, thanks a lot for your query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources