Filter IDs with just numbers excluding letters

Question

So I have results that begins with 2 letters followed by 3 numbers, for example:

ID_Sample
AB001
BC003
AB100
BC400

How can I do a query that ignores the letters and just looks up the numbers to do a filter? For example:

WHERE ID_Sample >= 100

I tried using a "Replace" to get rid of known letters, but I figured there might be a better way. For example:

Select
   Replace(id_sample,'AB','')

Choosing the 3 numerals on the right would work too.

Sure, just included it. Tried using "Replace", figured I could just put that in a subfilter then filter based on the values. But, was hoping there was a simpler way. — 1.618, Oct 01 '19 at 23:01
Performance will be terrible in a `where` clause because it has to run the computation on every record in the table. — Dale K, Oct 01 '19 at 23:04
Possible duplicate of [T-SQL select query to remove non-numeric characters](https://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters) — Dale K, Oct 01 '19 at 23:04

score 2 · Accepted Answer · answered Oct 01 '19 at 23:03

2

For your sample data, you can just start at the third character and convert to a number:

where try_convert(int, stuff(ID_Sample, 1, 2, '')) > 100

Or, if you know that the number is 3 characters:

where try_convert(int, right(ID_Sample, 3)) > 100

answered Oct 01 '19 at 23:03

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Oct 02 '19 at 00:33

+1 for Gordon's answer. This is a fun problem that you can solve using TRANSLATE if you're using SQL 2017+.

First, in case you've never used it, Per BOL TRANSLATE:

Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.2

This:

SELECT TRANSLATE('123AABBCC!!!','ABC','XYZ');

Returns: 123XXYYZZ!!!

Here's the solution using TRANSLATE:

-- Sample Data
DECLARE @t TABLE (ID_Sample CHAR(6))
INSERT @t (ID_Sample) VALUES ('AB001'),('BC003'),('AB100'),('BC400'),('CC555');

-- Solution
SELECT 
  ID_Sample     = t.ID_Sample,
  ID_Sample_Int = s.NewString
FROM         @t AS t
CROSS JOIN  (VALUES('ABCDEFGHIJKLMNOPQRSTUVWXYZ', REPLICATE(0,26)))     AS f(S1,S2)
CROSS APPLY (VALUES(TRY_CAST(TRANSLATE(t.ID_Sample,f.S1,f.S2) AS INT))) AS s(NewString)
WHERE        s.NewString >= 100;

Without the WHERE clause filter you get:

ID_Sample ID_Sample_Int
--------- -------------
AB001     1
BC003     3
AB100     100
BC400     400
CC555     555

... the WHERE clause filters out the first two rows.

score 1 · Answer 3 · answered Oct 02 '19 at 10:34

Check these methods- Unit test also done!

Declare @Table as table(ID_Sample varchar(20))

set nocount on

Insert into @Table (ID_Sample)
Values('AB001'),('BC003'),('AB100'),('BC400')

--substring_method
select * from @Table 
where try_cast(substring(ID_Sample,3,3) as int) >100

--right_method
select * from @Table 
where try_cast(right(ID_Sample,3) as int) >100

--stuff_method
select * from @Table 
where try_cast(stuff(ID_Sample,1,2,'') as int) >100

--replace_method
select * from @Table 
where try_cast(replace(ID_Sample,left(ID_Sample,2),'') as int) >100

Filter IDs with just numbers excluding letters

3 Answers3