求教一个字符串截取的问题

[gonguui1982]

表A

ID name sex
1 41小明 男
2 100小红 女
3 000小刚 男


要求检索表A 里的name字段,要求只显示中文名字(前边的数字不显示)

name
小明
小红
小刚

[快溜]

select right(name,len(name)+1-PATINDEX('%[^0-9]%',name)) from tb

[guguda2008]

1楼最好加个CASE WHEN PATINDEX>0的判断

[cd731107]

select

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')
from a

[yixilan]

循环逐个字符取出来，判断是否是数字，如果是数字就CUT掉。下班了。明天发代码。

[yixilan]

[Quote=引用 1 楼 ssp2009 的回复:]
SQL code
select right(name,len(name)+1-PATINDEX('%[^0-9]%',name)) from tb
[/Quote]
好强大。
真不错。

[gogodiy]

create table t1
(
	id int,
	name varchar(20),
	sex varchar(2)
)
insert into t1
select 1, '41小明', '男' union all
select 2, '100小红', '女' union all
select 3, '000小刚', '男'
select * from t1

select case when PATINDEX('%[吖-做]%',name)>0 then RIGHT(name,len(name)-PATINDEX('%[吖-做]%',name)+1) end as name
from t1

[yixilan]

CREATE PROCEDURE [dbo].[yixl_proc]
@search_index int --No use
as
DECLARE @vs_name NVARCHAR(10) = NULL
DECLARE @vs_tmp NVARCHAR(10) = NULL
DECLARE @vn_index INT = 1
DECLARE @vn_flg INT = 0
BEGIN
DECLARE CUR_NEXT CURSOR FOR
SELECT T2 FROM TEST_YIXL ORDER BY T1
OPEN CUR_NEXT
FETCH NEXT FROM CUR_NEXT INTO @vs_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- PRINT @vs_name
SET @vn_index = 1
SET @vn_flg = 0
WHILE (@vn_index <= len(@vs_name) and @vn_flg <> 1)
BEGIN
-- print @vn_index
IF ISNUMERIC(SUBSTRING(@vs_name, @vn_index, 1)) <> 1
BEGIN
SET @vs_tmp = SUBSTRING(@vs_name, @vn_index, LEN(@vs_name) - @vn_index + 1)
SET @vn_flg = 1
-- print @vs_tmp
-- print @vn_flg
END
SET @vn_index = @vn_index + 1
END
FETCH NEXT FROM CUR_NEXT INTO @vs_name
END

CLOSE CUR_NEXT
DEALLOCATE CUR_NEXT

RETURN (1)
END
--@vs_tmp是最终要返回的结果

[rucypli]

41小明
这怎么存的这种乱格式啊

[--小F--]

--提取数字
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,'|')
--
GO