CODEWITHSUNDEEP
sqlsql-serverselectsql-server-2008-r2
MAK
MAK

Reputation: 7260

Condition check with LEFT function

I have the following sample data:

create table num_test
(
    numb int,
    names varchar(20)
);

insert into num_test values(123456,'ABC');
insert into num_test values(12345,'DCD');
insert into num_test values(1234567,'ECD');
insert into num_test values(123456789,'BCD');

I have the input value:

declare @num int = 123456

I want to search for left 6 or 5 integers. If match with left 6 the left 5 should be ignored else left 5 should show.

So the expected result should be:

numb    names
-----------------
123456  ABC

Query:

declare @num int = 123456

SELECT * FROM num_test 
where numb = LEFT(@num,6) or numb = LEFT(@num,5)

Upvotes: 1

Views: 53

Answers (1)

Mureinik
Mureinik

Reputation: 311498

You could order the results by the length of numb and take the top one:

SELECT   TOP 1 * 
FROM     num_test 
WHERE    numb IN (LEFT(@num,6), LEFT(@num,5))
ORDER BY LEN(numb) DESC

Upvotes: 2

Related Questions