SQL LOOP - 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 2008 Forums

Transact-SQL (2008)

SQL LOOP

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-02-09 : 18:07:18

This is my issue. I have a table that has a field called npannxy that contains 6 and 7 digits

270361
2703612
2703618

I am trying to write a query that will get the closest match.

Example 1 - I have the number 2703612040
Example 2 - I have the number 2703613020

I would like to loop starting and the 7th digit and if no match loop to 6th digit

LOOP = 7

SELECT *
FROM TABLE
where npanxxy = LEFT(2703612040,LOOP)

The above example would return 2703612 because it would find a match at the 7th digit

SELECT *
FROM TABLE
where npanxxy = LEFT(2703613020,LOOP)

The above example would return 270361 because it would find a match at the 6th digit

I hope I wrote this so whoever is reading could understand my issue

Thanks for the help,

Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 01:39:01

you dont need a loop for this. see below


declare @test table

(id int identity(1,1),
val bigint
)

insert into @test
values (270361),
(2703612),
(2703618)
declare @inp bigint

set @inp= 2703613020--2703612040

select top 1 * from @test 
where CAST(@inp AS varchar(1000)) LIKE CAST(val as varchar(50)) + '%' 
ORDER BY LEN(CAST(val as varchar(50))) DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources