求SQL语句:字符串倒数第二位为数字的自动变成空值,不为数字则取最后一位
例如:表a:字段为code,
2010-012-XXXX-1
2010-012-XXXX-2
2010-013-XXXX-XXXX
2010-014-XXXX-1
2010-014-XXXX-2
2010-014-XXXX-3预计得到的结果为:
code bh
2010-012-XXXX-1 1
2010-012-XXXX-2 2
2010-013-XXXX-XXXX 0
2010-014-XXXX-1 1
2010-014-XXXX-2 2
2010-014-XXXX-3 3请教各位如何实现?
例如:表a:字段为code,
2010-012-XXXX-1
2010-012-XXXX-2
2010-013-XXXX-XXXX
2010-014-XXXX-1
2010-014-XXXX-2
2010-014-XXXX-3预计得到的结果为:
code bh
2010-012-XXXX-1 1
2010-012-XXXX-2 2
2010-013-XXXX-XXXX 0
2010-014-XXXX-1 1
2010-014-XXXX-2 2
2010-014-XXXX-3 3请教各位如何实现?
if object_id('[A1]') is not null drop table [A1]
create table [A1]([code] varchar(18))
insert [A1]
select '2010-012-XXXX-1' union all
select '2010-012-XXXX-2' union all
select '2010-013-XXXX-XXXX' union all
select '2010-014-XXXX-1' union all
select '2010-014-XXXX-2' union all
select '2010-014-XXXX-3'select * from [A1]SELECT *,CASE WHEN ISNUMERIC(RIGHT(code,1)) =1 THEN RIGHT(code,1) ELSE 0 END
FROM A1/*code
------------------ -----------
2010-012-XXXX-1 1
2010-012-XXXX-2 2
2010-013-XXXX-XXXX 0
2010-014-XXXX-1 1
2010-014-XXXX-2 2
2010-014-XXXX-3 3(6 行受影响)
*/
select code,case when left(right(code,2),1) between '0' and '9' then null else right(code,1) end h from tb其中数字加引号,因为是字符么!