create table #1(id int,val varchar(10))
insert into #1
select 1,'ABCDEF' union all
select 2,'123ABC' union all
select 3,'A2B2C3' union all
select 4,'A3B2C3'
a.查询val中的第二个字符是2的记录(请至少两种写法写)
b.返回结果中val中的值都是倒置, 如ID=1的val,返回是FEDCBA.
c.查询val中有数字的结果输出来.
d.val值中的数据若不满10位,则在字符前用0补齐。
e.求出长度最长的val的id
insert into #1
select 1,'ABCDEF' union all
select 2,'123ABC' union all
select 3,'A2B2C3' union all
select 4,'A3B2C3'
a.查询val中的第二个字符是2的记录(请至少两种写法写)
b.返回结果中val中的值都是倒置, 如ID=1的val,返回是FEDCBA.
c.查询val中有数字的结果输出来.
d.val值中的数据若不满10位,则在字符前用0补齐。
e.求出长度最长的val的id
insert into #1
select 1,'ABCDEF' union all
select 2,'123ABCf' union all
select 3,'A2B2C3' union all
select 4,'A3B2C3'
go
--a.
select * from #1 where SUBSTRING(val,2,1)='2'
select * from #1 where val like '_2%'
--b.
select id,REVERSE(val) from #1
--c.
select * from #1 where val like '%[0-9]%'
--d.
select id,RIGHT('000000000'+val,10) from #1
--e.
select top 1 ID from #1 order by LEN(val) desc
/*
id val
----------- ----------
2 123ABCf
3 A2B2C3(2 行受影响)id val
----------- ----------
2 123ABCf
3 A2B2C3(2 行受影响)id
----------- ----------
1 FEDCBA
2 fCBA321
3 3C2B2A
4 3C2B3A(4 行受影响)id val
----------- ----------
2 123ABCf
3 A2B2C3
4 A3B2C3(3 行受影响)id
----------- -------------------
1 0000ABCDEF
2 000123ABCf
3 0000A2B2C3
4 0000A3B2C3(4 行受影响)ID
-----------
2(1 行受影响)*/
go
drop table #1
set val = right('0000000000'+val,10)
where len(val) < 105、select top 1 id
from #1
order by len(val) desc
select * from #1 where charindex('2',val)>0
--c.
select * from #1 where patindex('%[1-9]%',val) > 0其他的不写了