create table tb(texts varchar(100))
insert into tb
select '1.hello! 你好' union all
select '2.I am fine 我很好!' union all
select '3.nice to meet you,yun 很高兴见到你,晕.'select * from tb我现在要变成col1 col2 col3
1 hello! 你好
2 I am fine 我很好!
3 nice to meet you,yun 很高兴见到你,晕.
insert into tb
select '1.hello! 你好' union all
select '2.I am fine 我很好!' union all
select '3.nice to meet you,yun 很高兴见到你,晕.'select * from tb我现在要变成col1 col2 col3
1 hello! 你好
2 I am fine 我很好!
3 nice to meet you,yun 很高兴见到你,晕.
select * from tb where texts like '%[吖-座]%'
--如果是固定形式，就可以这样:
create table tb(texts varchar(100))
insert into tb
select '1.hello! 你好' union all
select '2.I am fine 我很好!' union all
select '3.nice to meet you,yun 很高兴见到你,晕.'
select col1=substring(texts,1,charindex('.',texts)-1),
col2=substring(texts,patindex('%[a-zA-Z]%',texts),patindex('%[吖-咗]%',texts)-patindex('%[a-zA-Z]%',texts)-1),
col3=substring(texts,patindex('%[吖-咗]%',texts)+1,len(texts)) from tb--结果：
col1 col2 col3
------- ----------------------- -------------------------
1 hello! 好
2 I am fine 很好!
3 nice to meet you,yun 高兴见到你,晕.
--少取了个汉字,改下:
select col1=substring(texts,1,charindex('.',texts)-1),
col2=substring(texts,patindex('%[a-zA-Z]%',texts),patindex('%[吖-咗]%',texts)-patindex('%[a-zA-Z]%',texts)),
col3=substring(texts,patindex('%[吖-咗]%',texts),len(texts)) from tb
SQL code--提取数字
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--提取中文
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
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--过滤重复字符
IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT+@S
IF RIGHT(@S,1)<>@SPLIT
SET @S = @S+@SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
SET @INDEX = CHARINDEX(@SPLIT,@S)
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF @NEW IS NULL
SET @NEW = ISNULL(@NEW,'')+@TEMP
ELSE
SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILE CHARINDEX(@TEMP,@S)>0
BEGIN
SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
END
END
RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')
--A,B,C
GO
--------------------------------------------------------------------
--过滤重复字符2
IF OBJECT_ID('DBO.DISTINCT_STR2') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR2
GO
CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
WHILE LEN(@S)>0
BEGIN
SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)
SET @S=REPLACE(@S,LEFT(@S,1),'')
END
RETURN @NEW
END
GO
SELECT DBO.DISTINCT_STR2('AABCCD')
--ABCD
GO--------------------------------------------------------------------
IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar(8000), --包含多个数据项的字符串
@INDEX int, --要获取的数据项的位置
@SPLIT varchar(10) --数据分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
END
GO
PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
--
GOshijiazhuang 石家庄
hefei 合肥
很完美了，不过有些后面没有汉字的有几白条，那col3就显示空白，帮助一起搞定吧？tks
returns varchar(400)
as
begin
if @type = 1
begin
while patindex('%[吖-座]%',@str)>0
begin
set @str = stuff( @str, patindex('%[吖-座]%',@str) ,1 ,'')
end
end
else
begin
while patindex('%[^吖-座]%',@str)>0
begin
set @str = stuff( @str, patindex('%[^吖-座]%',@str) ,1 ,'')
end
end
return @str
end
gocreate table tb(texts varchar(100))
insert into tb
select '1.hello! 你好' union all
select '2.I am fine 我很好!' union all
select '3.nice to meet you,yun 很高兴见到你,晕.'select * from tb
select texts,dbo.f_str(texts,1) ,dbo.f_str(texts,0)
from tbgo
drop table tb
drop function f_str
/*
texts
----------------------------------------------------------------------------------------------------
1.hello! 你好
2.I am fine 我很好!
3.nice to meet you,yun 很高兴见到你,晕.(3 行受影响)texts
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.hello! 你好 1.hello! 你好
2.I am fine 我很好! 2.I am fine ! 我很好
3.nice to meet you,yun 很高兴见到你,晕. 3.nice to meet you,yun ,. 很高兴见到你晕(3 行受影响)*/
create table tb(texts varchar(100))
insert into tb
select '1.hello! 你好' union all
select '2.I am fine 我很好!' union all
select '3.nice to meet you,yun 很高兴见到你,晕.' union all
select '3.nice to meet you,yun 'select col1=substring(texts,1,charindex('.',texts)-1),
col2=substring(texts,patindex('%[a-zA-Z]%',texts),case patindex('%[吖-咗]%',texts) when 0 then len(texts) else patindex('%[吖-咗]%',texts)-patindex('%[a-zA-Z]%',texts) end),
col3=case patindex('%[吖-咗]%',texts) when 0 then '' else substring(texts,patindex('%[吖-咗]%',texts),len(texts)) end
from tb
----------------------------------------------------------------------------------------------------
1.hello!
12.hello! 好
2.I am fine 我很好
3.nice to meet you,yun 很高兴见到你,晕（所影响的行数为 4 行）texts
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.hello! 1.hello!
12.hello! 好 12.hello! 好
2.I am fine 我很好 2.I am fine 我很好
3.nice to meet you,yun 很高兴见到你,晕 3.nice to meet you,yun , 很高兴见到你晕（所影响的行数为 4 行）