SQL查询前两个数值是字母的列,比如TC0001,TC0002,0056,3396,48454.......

Question

ID NUM
1 TC0001
2 TC0002
3 00666
4 69521
5 HK6549
...............

把NUM前面两位是字母的找出来.

数据库是IBM DB2

Answer 1

wzsjzjjxy的回答，应该有一个转化表达式的函数吧？另外这个是不是在老版本的数据库没有啊？
woshifotuo，asciistr(SUBSTRING(SUM,1,1)) not like '\%' ，这个语法只能判断是以非汉字开头，而不能判断是以字母开头。如果是以数字开头，也会查询出来的。
我觉得应该用ASCII码的范围来限制合适。

select *
from TABLE
where ((ASCII(SUBSTR(NUM,1,1)) between 97 and 122) OR (ASCII(SUBSTR(NUM,1,1)) between 65 and 90))
AND (ASCII(SUBSTR(NUM,2,1)) >=97 and ASCII(SUBSTR(NUM,2,1))<=122 OR ASCII(SUBSTR(NUM,2,1)) between 65 and 90)
分别判断第一位和第二位的ASCII码，65-90是大写的A-Z， 97-122是小写的a-z
以上SUBSTR是ORACLE函数，如果SQLSERVER，请用SUBSTRING

DB2字符串截取的函数也是SUBSTR

Answer 2

试看下面的语句
select *
from table
where (Left(NUM, 1) >='A' and Left(NUM, 1) <= 'Z' Or Left(NUM, 1) >='a' and Left(NUM, 1) <= 'z')
and (Substring(NUM, 2, 1) >='A' and Substring(NUM, 2, 1) <= 'Z' Or Substring(NUM, 2, 1) >='a' and Substring(NUM, 2, 1) <= 'z')

Answer 3

只是选择出NUM前面两位是字母的列
SELECT NUM
FROM TableName
WHERE isnumeric（NUM） = 0

Answer 4

select ID,SUM from tableName where asciistr(SUBSTRING(SUM,1,1)) not like '\%' and SUM(SUBSTRING(SUM,2,1)) not like '\%'

Answer 5

select * from tablename where num like '[A-Z][A-Z]%'追问

select * from tablename where num like 'AA%'
这样就可以

select * from tablename where num like '[A-Z][A-Z]%'
这样不行啊.

追答

你是什么数据库？sqlserver的话是可以的。[a-z]表示字母abcd.....z中任意一个字母。

追问

数据库是DB2的。

追答

DB2就不知道了。