bmsqldev bmsqldev - 5 months ago 5
SQL Question

Eliminate the Records based on the value in sql server

I have a

table
as below,

id record

1 5000tax
2 tax5000
3 tax5001
4 taxpro
5 val5005


I need to retrieve the records which started with letters. i wrote below query

select id, record, right(record,4) as code
from table
where left(record,1) like 'A' and 'Z'


output:

id record code

2 tax5000 5000
3 tax5001 5001
4 taxpro xpro
5 val5005 5005


here i don't want to include the
records
which doesn't have any numbers in it.
i.e xpro.

I wrote below query

select id, record, right(record,4) as code
from table
where left(record,1) like 'A' and 'Z'
and record between '0' and '9'


It doesn't give any output.

thanks for the help

( P.S : Full text search property is disabled in the table)

Answer

You can use [0-9] with LIKE:

select id, record, right(record, 4) as code
from table
where record like '[A-Z]%'
  and record like '%[0-9]%';

Or if it's always four digit numbers at the end, then:

select id, record, right(record, 4) as code
from table
where record like '[A-Z]%'
  and record like '%[0-9][0-9][0-9][0-9]';
Comments