sql - SQL：从 sql 列值中检索前三个字符

Question

例如：如果 sql 列值为 sa,123k 并且输出应该是前三个字符，即 sak

需要消除字母和任何特殊字符，并且只得到三个字符。我们如何做到这一点 ？

标签： sqlsql-server

Answer 1

为此，您可以使用递归 CTE：

with t as (
      select 'sa,123k' as str
     ),
     cte as (
      select str, left(str, 1) as c, stuff(str, 1, 1, '') as rest, 1 as lev,
             convert(varchar(max), (case when left(str, 1) like '[a-zA-Z]' then left(str, 1) else '' end)) as chars
      from t
      union all
      select str, left(rest, 1) as c, stuff(rest, 1, 1, '') as rest, lev + 1,
             convert(varchar(max), (case when left(rest, 1) like '[a-zA-Z]' then chars + left(rest, 1) else chars end))
      from cte
      where rest > '' and len(chars) < 3
     )
select str, max(chars)
from cte
where len(chars) <= 3
group by str;

这是一个 db<>fiddle。