SQL Like Operator to Get the Numbers Only

sql like operator to get the numbers only

You can try this

ISNUMERIC (Transact-SQL)

ISNUMERIC returns 1 when the input
expression evaluates to a valid
numeric data type; otherwise it
returns 0.

DECLARE @Table TABLE(
        Col VARCHAR(50)
)

INSERT INTO @Table SELECT 'ABC' 
INSERT INTO @Table SELECT 'Italy' 
INSERT INTO @Table SELECT 'Apple' 
INSERT INTO @Table SELECT '234.62' 
INSERT INTO @Table SELECT '2:234:43:22' 
INSERT INTO @Table SELECT 'France' 
INSERT INTO @Table SELECT '6435.23'
INSERT INTO @Table SELECT '2' 
INSERT INTO @Table SELECT 'Lions'

SELECT  *
FROM    @Table
WHERE   ISNUMERIC(Col) = 1

Like operator for integer

If you must use LIKE, you can cast your number to char/varchar, and perform the LIKE on the result. This is quite inefficient, but since LIKE has a high potential of killing indexes anyway, it may work in your scenario:

... AND CAST(phone AS VARCHAR(9)) LIKE '%0203'

If you are looking to use LIKE to match the beginning or the end of the number, you could use integer division and modulus operators to extract the digits. For example, if you want all nine-digit numbers starting in 407, search for

phone / 1000000 = 407

SQL Server : How to test if a string has only digit characters

Use Not Like

where some_column NOT LIKE '%[^0-9]%'

Demo

declare @str varchar(50)='50'--'asdarew345'

select 1 where @str NOT LIKE '%[^0-9]%'

SQL LIKE condition to check for integer?

That will select (by a regex) every book which has a title starting with a number, is that what you want?

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

Check if a string contains only number

I would suggest try_convert():

select (case when try_convert(col, float) is not null then 'valid' else 'invalid' end)

The one possible downside is exponential format; 1e6 is a valid number for instance.

An alternative is the where approach; you just need more complete logic:

select (case when col like '%[^0-9.]%' then 'invalid'
             when col like '%.%.%' then 'invalid'
             else 'valid'
        end)

SQL wildcard to fetch numbers only

LIKE doesn't support regular expressions in SQL.

You either need to use the standard compliant similar to or Postgres' proprietary ~ operator to compare against a regex.

where player_id similar to 'playerId-[0-9]+'

where player_id ~ '^playerId-[0-9]+$'

Note the difference in the regex: similar to doesn't need the "anchor" because it always does a full match.

If you want a case insensitive comparison, use ~* instead of ~

Getting only numbers after and before alphabets

The LIKE operator can work here:

SELECT *
FROM [teble1]
WHERE
    TELP LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]X[0-9]%';

The above like pattern says to capture any number having seven digits, followed by an X, followed by at least one digit afterwards.

SQL Server does not have very strong regex support, but to compensate for that, they enhanced LIKE to support some basic regex pattern matching, such as [0-9] to represent any digit.

---

Related Topics