处理字符串_7_判断字符串是字符串数字型

                           判断字符串是字符串数字型

需求描述

需求：检索temp_str data表的字段data是字符串数字类型的记录这里如果都是字符串或者数字的也符合条件.

解决方法:这里通过translate、replace、repeate（replicate、rpad）函数对含数字的部分进行替换.

注: 数据库数据集SQL脚本详见如下链接地址

​​员工表结构和数据初始化SQL脚本​​

SQL代码

--Sql Server：
CREATE TABLE temp_str(data VARCHAR(1000));

INSERT INTO temp_str VALUES('SMITH20');
INSERT INTO temp_str VALUES('JONES30');
INSERT INTO temp_str VALUES('Jim#40');
INSERT INTO temp_str VALUES('50$Tom');
INSERT INTO temp_str VALUES('60:Mike');
INSERT INTO temp_str VALUES('70Cruz');
INSERT INTO temp_str VALUES('Jack');
INSERT INTO temp_str VALUES('J8oh0n');

-- Sql Server版translate函数定义详见如下注释内代码
/*
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[translate](
 @string VARCHAR(MAX),
 @from_str VARCHAR(MAX),
 @to_str VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  -- 返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。
  -- TRANSLATE 是 REPLACE 所提供的功能的一个超集。
  -- 如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。
  -- to_str 不能为空。
  -- Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。
  IF @string IS NULL OR @from_str IS NULL OR @to_str IS NULL
  BEGIN
    RETURN NULL;
  END;
  -- 源长度与目标长度
  DECLARE @FromLen INT, @ToLen INT;
  SET @FromLen = LEN(@from_str);
  SET @ToLen = LEN(@to_str);
  -- 准备用于返回的数值.
  DECLARE @resultVal VARCHAR(MAX);
  SET @resultVal = @string;
 
  -- 用于存储 本次需要替换的字符信息.
  DECLARE @thisTimeReplace CHAR(1);
 
  -- 从后向前依次替换.
  WHILE @FromLen > 0
  BEGIN
   
    -- 取得本次即将要替换的字符.
    SET @thisTimeReplace = SUBSTRING(@from_str, @FromLen, 1);
   
    IF CHARINDEX(@thisTimeReplace, @from_str) < @FromLen
    BEGIN
      -- 假如当前这个要替换的字符，在前面还有，那么这里就不替换了
      -- 原因，为了支持
      -- SELECT TRANSLATE('2KRW229', '1234567890' || '2KRW229',  '1234567890')
      -- 这样的效果.
     
      -- 向前处理上一个
    /*
    补充说明：
    1 理论上TRANSLATE函数的@from_str参数和@to_str参数的长度要一致,即一一映射。比如@from_str='0123',@to_str='abcd'
    2 该步骤旨在找到在@from_str参数里要替换的的字符重复指定了,比如'0123XYZ23'，这里23是重复指定了，对于重复指定的要舍弃所以需要在该步时需要将@FromLen锁定到字符Z的位置即7
    */
      SET @FromLen = @FromLen - 1;
      CONTINUE;
    END
    IF @FromLen > @ToLen
    BEGIN
      -- from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。
      SET @resultVal = REPLACE(@resultVal, SUBSTRING(@from_str, @FromLen, 1), '');
    END
    ELSE
    BEGIN
      -- from_str中的每个字符替换为to_str中的相应字符以后的string
    -- SELECT dbo.TRANSLATE('2KRW229', '12345678902KRS229','12345678902') 这里用S替换时其实无效
      SET @resultVal = REPLACE(@resultVal, @thisTimeReplace, SUBSTRING(@to_str, @FromLen, 1));
    END;
   
    -- 处理完当前字符后，向前处理上一个.
    SET @FromLen = @FromLen - 1;
  END;
 
  -- 依次处理完毕后，返回结果.
  RETURN @resultVal;
 
END


*/


SELECT data --,dbo.translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPLICATE('a',36)) as trans
FROM temp_str
WHERE dbo.translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPLICATE('a',36))=REPLICATE('a',LEN(data))


-- 或者用如下方法,思路通过判断截取的每个字符的ASCII值来判断区间是否落在[48,57],[97,122],落在的为0,反之为1,然后再通过对该Flag进行sum来判断,如果等于0则说明数字字符型的.

SELECT data -- ,SUM(Flag) 
FROM 
(
SELECT LOWER(data) as data,SUBSTRING(e.data,seq.pos,1) AS ename_Split,ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) AS asci,
CASE WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) >= 48 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=57 THEN 0
WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) >= 97 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=122 THEN 0 ELSE 1 END AS Flag
FROM temp_str e,
(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0) seq
WHERE seq.pos<=LEN(e.data)
-- ORDER BY data
)A
GROUP BY data
HAVING SUM(Flag) = 0

--Mysql:
SELECT data -- ,translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPEAT('a',36)) AS trans
FROM temp_str
WHERE  translate(UPPER(data),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',REPEAT('a',36))=REPEAT('a',LENGTH(data))

-- 利用正则表达式来匹配
SELECT data 
FROM temp_str
WHERE data regexp '[^0-9A-Za-z]' = 0

-- 待验证Mysql8
WITH RECURSIVE cte (num) AS
(
  SELECT 1
  UNION ALL
  SELECT num + 1 FROM cte WHERE num < 100
)
SELECT data -- ,SUM(Flag) 
FROM 
(
SELECT LOWER(data) as data,SUBSTRING(e.data,seq.pos,1) AS ename_Split,ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) AS asci,
CASE WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) >= 48 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=57 THEN 0
WHEN ascii(SUBSTRING(LOWER(e.data),seq.pos,1)) >= 97 AND ascii(SUBSTRING(LOWER(e.data),seq.pos,1))<=122 THEN 0 ELSE 1 END AS Flag
FROM temp_str e,
(SELECT num FROM cte) seq
WHERE seq.pos<=LEN(e.data)
-- ORDER BY data
)A
GROUP BY data
HAVING SUM(Flag) = 0

执行结果