SQL - Select highest value from table where the value matches a format?

Question

I have a variable that returns a format something like variable = '10100001001'

I want to select the highest value(max) from a column (char 20) where the value matches the format of the above variable.

Matches -- might be the length, etc.,

I have seen similar kind of questions to select the max value but not matches a format.

So something like Select value from Table1 where value like ('%variable%')

I would like to know is there any more obvious and elegant way to get this.

sample data:

id    value
--  -- -- -- --
1   101000019
2   abcd123gh 
3   101000026 
4   abcd
5    23433
6   10100gh34

Desired results:

101000026

Thanks

Sample data, desired results... You should know this by now! — Zohar Peled, Jul 27 '17 at 16:09
What in case no matching result? Assume I am looking for 2412e11, what is your expected result? — Mocas, Jul 27 '17 at 16:53
I don't know if your expected results will be correct with 101000026 in the example above. Since your like '%variable%' will bring in ID 1,3 and 6. In this particular sample, what is the MAX value? Since you have a Num and VARCHAR stored on the same column. Do you only want the MAX of a NUM? — Isaiah3015, Jul 27 '17 at 17:00
Could you up update your sample data layout - looks like 'value' is 4 columns of length=2, id=5 looks like value has a leading blank - ' 23433'; and what is the variable/format you're using to generate the desired result? — markp-fuso, Jul 27 '17 at 17:02

score 1 · Accepted Answer · answered Jul 27 '17 at 17:21

NOTE: Not sure what you mean by "Matches -- might be the length", so ignoring that tidbit for time being ...

If all you're doing is a like pattern match you can just append the % on the ends of @variable, either by updating the variable (select @variable = '%' + @variable + '%') or in the WHERE clause (see below).

Here's a quick example ...

Setup:

create table Table1 (id int, value varchar(20))
go

insert Table1 values (1,'101000019')
insert Table1 values (2,'abcd123gh')
insert Table1 values (3,'101000026')
insert Table1 values (4,'abcd')
insert Table1 values (5,' 23433')
insert Table1 values (6,'10100gh34')
go

select * from Table1
go

 id          value
 ----------- --------------------
           1 101000019
           2 abcd123gh
           3 101000026
           4 abcd
           5  23433
           6 10100gh34

Example searches:

declare @variable varchar(20)

select  @variable = '10100001001'
print "++++++++++++ pattern : %1!", @variable
select max(value) from Table1 where value like '%' + @variable + '%'

select  @variable = '101000'
print "++++++++++++ pattern : %1!", @variable
select max(value) from Table1 where value like '%' + @variable + '%'

select  @variable = '10100'
print "++++++++++++ pattern : %1!", @variable
select max(value) from Table1 where value like '%' + @variable + '%'
go

++++++++++++ pattern : 10100001001
 max(Table1.value)
 --------------------
 NULL

++++++++++++ pattern : 101000
 max(Table1.value)
 --------------------
 101000026

++++++++++++ pattern : 10100
 max(Table1.value)
 --------------------
 10100gh34

SQL - Select highest value from table where the value matches a format?

sample data:

Desired results:

1 Answers1