D.Loo D.Loo - 2 months ago 10
SQL Question

Select the rows where variable x starts with 65 (teradata)

I have a table with a column named x that includes numbers for all my observations. I now want to select only the variables that start with 65.

I've tried:

SELECT * FROM table
WHERE x REGEXP '^[65]'


and different versions of like/isnumeric, but I cant figure a clean way out.

Answer

If this is actually a Teradata DBMS your inital query will result in an error message because there's no REGEXP (but there's a REGEXP_SIMILAR).

You don't need a regular expression to compare the first two digits. If the datatype of x is numeric you must cast it to a string first:

WHERE TRIM(x) LIKE '65%'
WHERE CAST(x AS VARCHAR(20)) LIKE '65%'

If it's a VarChar you might have some leading spaces (which are really bad):

WHERE TRIM(x) LIKE '65%'