SQL - Select like string

Question

I have the following table:

create table #tbl
(
    PartNumber varchar(20)
)

insert into #tbl values ('003A-I00-1')
insert into #tbl values ('003A-INT-1')
insert into #tbl values ('003A-I1')
insert into #tbl values ('003A-I2')
insert into #tbl values ('003A-I3')

I need to select the highest PartNumber where PartNumber is equal to 003A-I followed only by a number (or numbers). In other words, I need 003A-I3.

What I've tried:

select top 1 partnumber
from #tbl
where partnumber like '003A-I%' + '%[0-9]'
order by partnumber desc

But it doesn't work. It returns 003A-INT-1. I need 003A-I3.

I'm using MS SQL Server 2005.

Mitch Wheat · Accepted Answer · 2014-01-04 04:45:40Z

2

select top 1 partnumber
from tbl
where partnumber like '003A-I[0-9]%'
order by partnumber desc

SQLFiddle

Update (in response to @Nicarus's comment:

SELECT top 1 partnumber
FROM tbl
WHERE partnumber LIKE '003A-I[0-9]%'
ORDER BY convert(varbinary(200), partnumber) DESC

edited Jan 4, 2014 at 4:45

answered Jan 4, 2014 at 4:25

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2839610 Over a year ago

If you have a different collation set, you could get different results because you are ordering by text

CodingMate · Accepted Answer · 2014-01-04 04:58:10Z

-1

If you want to get the numeric values, you can use the ISNUMERIC() function of the SQL The code will be:

select * from #tbl
where isnumeric(replace(partnumber, '003A-I', '')) = 1
order by partnumber desc

REVISE:

ISNUMERIC() function returns Boolean value either true or false (1 or 0). REPLACE() function replaces a certain pattern in an expression or string

For more information:

http://technet.microsoft.com/en-us/library/ms186862.aspx

http://technet.microsoft.com/en-us/library/ms186272.aspx

UPDATE (2): You can use the following if you don't want to match a specific word, but as long as you know the text size which would be replaced

select top 1 * from #tbl
where isnumeric(substring(partnumber, 7, LEN(partnumber))) = 1
order by partnumber desc

The link on the SQLFiddle

edited Jan 4, 2014 at 4:58

answered Jan 4, 2014 at 4:30

CodingMate

3434 silver badges16 bronze badges

4 Comments

PM 77-1 Over a year ago

Will match 103A-I (for example).

CodingMate Over a year ago

If you're talking about the REPLACE() function, yes it can easily math that or other text. Or a generic solution if you know the size of the text before the Number you can use the SUBSTRING() function. For an example SUBSTRING(partnumber, 0, 5) and use it inside the ISNUMERIC() function

Mitch Wheat Over a year ago

alos returns all matches...not just Max

CodingMate Over a year ago

@MitchWheat You can easily select top 1, since you are order by partnumber desc. If using the SUBSTRING() function, the full code will be as pasted in my answer

Collectives™ on Stack Overflow

SQL - Select like string

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related