一些难点的函数使用题^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Question

在项目中碰到有这样的情况,有一列数据类型是字符型
记录如下:
 
id    mname
1     ab13
2     abc
3     sime
4     123  
5     124ab
6     中华
7     2009-02-02
8     12
9     abc
10    a%-bc
11    a_bc
现在要得出这几种情况
一种,查出全是字母的组合的
select * from 表 where patindex('%[0-9]%',mnane)=0
二种,查出全是数字组合的数
select * from 表 where ISNUMERIC(mname) = 0
三种,查出字母与数字共同组合的数
select * from 表 where patindex('%[0-9]%',mnane)>0
四种,查出属于中文类型字符的数四种,查出日期格式的数五种,把字母与数字组合的数拆分成两列(一列存字符,一列存数字)六种,查出带特殊符号的数如%,_等这种情况是少见,但我们项目中有这种情况出现,这数是导入进去的,现在就要我们来排查这几种情况 上面我做出两种,看大家还有别的思路吗
高手能教教我吗??/

Answer 1

三种,查出字母与数字共同组合的数 
select * from 表 where patindex('%[0-9]%',mnane)>0 and patindex('%[a-zA-Z]%',mnane)>0

Answer 2

中文select * from tb where PATINDEX('%[^吖-咗]%',列名) =0

Answer 3

DECLARE @TB TABLE(ID INT,ANAME VARCHAR(50))
INSERT INTO @TB SELECT 1,'ab13' 
UNION ALL SELECT 2,'abc' 
UNION ALL SELECT 3,'me'
UNION ALL SELECT 4,'3'
UNION ALL SELECT 5,'4ab' 
UNION ALL SELECT 6,'中华'
UNION ALL SELECT 7,'09-02-02 '
UNION ALL SELECT 8,'12'
UNION ALL SELECT 9,'c'
UNION ALL SELECT 10,'-bc'
UNION ALL SELECT 11,'b%c'--一种,查出全是字母的组合的
SELECT * FROM @TB WHERE NOT PATINDEX('%[^a-z]%',ANAME)>0--二种,查出全是数字组合的数 
SELECT * FROM @TB WHERE ISNUMERIC(ANAME)=1--三种,查出字母与数字共同组合的数
SELECT * FROM @TB WHERE NOT PATINDEX('%[^0-9a-z]%',ANAME)>0--四种,查出属于中文类型字符的数
SELECT * FROM @TB WHERE  PATINDEX('%[啊-座]%',ANAME)>0  --四种,查出日期格式的数
SELECT * FROM @TB WHERE ISDATE(ANAME)=1--六种,查出带特殊符号的数如%,_等
SELECT * FROM @TB WHERE  PATINDEX('%[%,-]%',ANAME)>0  /*ID          ANAME                                              
----------- -------------------------------------------------- 
2           abc
3           me
9           c（所影响的行数为 3 行）ID          ANAME                                              
----------- -------------------------------------------------- 
4           3
8           12（所影响的行数为 2 行）ID          ANAME                                              
----------- -------------------------------------------------- 
1           ab13
2           abc
3           me
4           3
5           4ab
8           12
9           c（所影响的行数为 7 行）ID          ANAME                                              
----------- -------------------------------------------------- 
6           中华（所影响的行数为 1 行）ID          ANAME                                              
----------- -------------------------------------------------- 
7           09-02-02 （所影响的行数为 1 行）ID          ANAME                                              
----------- -------------------------------------------------- 
7           09-02-02 
10          -bc
11          b%c（所影响的行数为 3 行）
*/

Answer 4

select * from tb where isdate(列名)=1
--时间列

Answer 5

四种,查出属于中文类型字符的数 --提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-咗]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-咗]%',@S),1,N'')
RETURN @S
END
GO
select len(DBO.CHINA_STR('呵呵ABC123ABC'))提取后用len()

Answer 6

--四种,查出属于中文类型字符的数 
select * from ta where patindex('%[吖-帀]%',mname)>0 

Answer 7

create table #tmp (id int,mname varchar(20))
insert into #tmp (id,mname)
select 1    ,'ab13' 
union select 2    ,'abc' 
union select 3    ,'sime' 
union select 4    ,'123'  
union select 5    ,'124ab' 
union select 6    ,'中华' 
union select 7    ,'2009-02-02' 
union select 8    ,'12' 
union select 9    ,'abc' 
union select 10    ,'a%-bc' 
union select 11 ,'a_bc' 
union select 12, '1998/03/31'--四种,查出属于中文类型字符的数 
select * from #tmp where patindex('%[吖-座]%',mname)>0 
--四种,查出日期格式的数 
select * from #tmp where mname like '[1-2][0-9][0-9][0-9][-/][0-1][0-9][-/][0-3][0-9]'--五种,把字母与数字组合的数拆分成两列(一列存字符,一列存数字) 
--写一函数来实现--六种,查出带特殊符号的数如%,_等 
select * from #tmp where patindex('%[%]%',mname)>0

Answer 8

四种,查出属于中文类型字符的数 
select * from 表 where mname like '%[吖-座]%'四种,查出日期格式的数 
select * from 表 where isdate(mname)五种,把字母与数字组合的数拆分成两列(一列存字符,一列存数字) 
;with s as (
select id,mname,0 as n,cast('' as varchar(10)) as sCol,cast('' as varchar(10)) as nCol from 表
union all
select id,mname,n+1 as n,
cast((sCol + case when isnumeric(substring(mname,n+1,1))=1 then '' else substring(mname,n+1,1) end) as varchar(10)) as sCol,
cast((nCol + case when isnumeric(substring(mname,n+1,1))=1 then substring(mname,n+1,1) else '' end) as varchar(10)) as nCol
from s where n<=len(sCol+nCol)
)
select id,mname,sCol,nCol from s where n=len(mname)六种,查出带特殊符号的数如%,_等 
select * from 表 where mname like '%[%,_]%'

Answer 9

加几问第七种,查询出各组合中元音字母的个数,第八问,给每个组合数排序,例 第一行 mname  排序成 aemmn 再展示出来第九问,暂时还未想到,
贴子放几天,

Answer 10

create table tt
(
  id int,
  Nname varchar(30)
)
insert into tt select 1,'ab13'
insert into tt select 2,'abc' 
insert into tt select 3,'me'
insert into tt select 4,'3'
insert into tt select 5,'4ab' 
insert into tt select 6,'中华'
insert into tt select 7,'09-02-02'
insert into tt select  8,'12'
insert into tt select  9,'c'
insert into tt select  10,'-bc'
insert into tt select  11,'b%c'--一种,查出全是字母的组合的
select * from tt where patindex('%[^a-z]%',Nname)=0
--二种,查出全是数字组合的数
select * from tt where isnumeric(Nname)=1
----三种,查出字母与数字共同组合的数
select * from tt where patindex('%[0-9]&&[a-Z]%',Nname)=0
--查出日期格式的数
select * from tt where isdate(Nname)=1