I am a beginner with regex query therefore I want to ask you how to convert this regex query in Oracle to SQL Server?
select *
from Sales
where regexp_like(productname,'^[A-Z]{3}[0-9]+$')
I convert it to this query:
select *
from Sales
where substr(productname, 1, 3) in ([A-Z])
Is that correct?
Thankyou.
You can use the following query:
SELECT *
FROM Sales
WHERE LEFT(productname, 3) LIKE '[A-Z][A-Z][A-Z]' -- three chars ([A-Z]{3})
AND NOT RIGHT(productname, LEN(productname) - 3) LIKE '%[^0-9]%' -- only numbers after the first three chars
AND NOT LEN(RIGHT(productname, LEN(productname) - 3)) = 0 -- at least one number
WHERE LEFT(productname, 3) LIKE '[A-Z][A-Z][A-Z]' AND NOT RIGHT(productname, LEN(productname) - 3) LIKE '%[^0-9]%' )
ABC would match without AND NOT LEN(RIGHT(productname, LEN(productname) - 3)) = 0 too. But the original regex is ^[A-Z]{3}[0-9]+. So the value have to be ABC and the fourth char should be a number.
%[^0-9]%) match all characters but not numbers (0 to 9) or a empty string.Based on regexp '^[A-Z]{3}[0-9]+$':
-- exactly 3 letters
-- at least one trailing digits
You could use TRIM:
SELECT *
FROM Sales
WHERE productname LIKE '[A-Z][A-Z][A-Z][0-9]%'
AND TRIM( '0123456789' FROM productname) LIKE '[A-Z][A-Z][A-Z]';
-- warning! expression is not SARGable
Think the most simple would be to use
WHERE
SUBSTRING(<column>, 1, 1) IN('A', 'B', 'C')
AND
SUBSTRING(<column>, 2, 1) IN('A', 'B', 'C')
AND
SUBSTRING(<column>, 2, 1) IN('A', 'B', 'C')
AND
TRY_CONVERT(
INT
, SUBSTRING(<column>, 4, LEN(<column>))
) <> 0
as you don't really need regex to do this.
see demo
