34,590
社区成员
发帖
与我相关
我的任务
分享
;WITH CTE AS
(SELECT
'123 aabb 上海市火车站 990' AS Col1
UNION ALL
SELECT 'ab 898 ss9999 北京aaa'
UNION ALL
SELECT '12345678 天津bbbb'
UNION ALL
SELECT '12345678feaf'
)
SELECT LEFT(CTE.Col1,PATINDEX('%[吖-座]%',CTE.Col1+'吖')-1)
FROM CTE
--------------------------
123 aabb
ab 898 ss9999
12345678
12345678feaf
with table1 as
(
SELECT '123 aabb 上海市火车站' id UNION ALL
SELECT 'ab 898 ss9999 北京' UNION ALL
SELECT '12345678 天津'
)
select id,MIN(number)number, SUBSTRING(id,1,min(number)-1) from (
select id,number
from table1 a
, (select number+1 number from master..spt_values where type='P' and number<1000) b
where number<=LEN(a.id)
) aa where DATALENGTH(SUBSTRING(id,number,1))<>1
group by id
--方法一:用函数实现
create function [dbo].[m_getchinese](
@chinese nvarchar(max) )
returns varchar(100)
as
begin
while PATINDEX('%[^吖-咗]%',@chinese) >0
begin
set @chinese = STUFF(@chinese,patindex('%[^吖-咗]%',@chinese),1,N'');
end
return @chinese
end
GO
--方法二:举例
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID int,Content varchar(13))
INSERT INTO @tb
SELECT 1,'abcd测试efg' UNION ALL
SELECT 2,'zzzz大家好yyy' UNION ALL
SELECT 3,'谢谢test'
--SQL查询如下:
SELECT *
FROM @tb AS A
OUTER APPLY (SELECT x=(SELECT SUBSTRING(A.Content,number,1) AS [text()]
FROM master.dbo.spt_values
WHERE type = 'p' AND SUBSTRING(A.Content,number,1)<>''
AND SUBSTRING(A.Content,number,1) LIKE '%[吖-咗]%'
FOR XML PATH(''),TYPE).value('.','varchar(500)')) AS B
/*
ID Content x
----------- ------------- ---------------------------
1 abcd测试efg 测试
2 zzzz大家好yyy 大家好
3 谢谢test 谢谢
(3 行受影响)
*/
--去中文函数
CREATE FUNCTION DBO.GetAlphabet(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[吖-座]%',@S),1,N'')
RETURN @S
END
GO
--测试
CREATE TABLE #TEMP(a NVARCHAR(100))
INSERT INTO #TEMP(a)
SELECT '123 aabb 上海市火车站' UNION ALL
SELECT 'ab 898 ss9999 北京' UNION ALL
SELECT '12345678 天津'
SELECT a,dbo.GetAlphabet(a) FROM #TEMP