P5T9*6*3C
P5T9.53*4.75*3.2C
W45RID9.37*5.35*8S
W45RID9.37*5.35*8S
A121RID9.37*5.35*8S我希望分别截取以上的字串为:
P5
P5
W45
W45
A121请问怎样截取?谢谢
P5T9.53*4.75*3.2C
W45RID9.37*5.35*8S
W45RID9.37*5.35*8S
A121RID9.37*5.35*8S我希望分别截取以上的字串为:
P5
P5
W45
W45
A121请问怎样截取?谢谢
P5
W45
W45
A121这几个值是固定的吗?
规则就是,前面字串是这样的:
1字符+几个数字+1到几个字符+再加数字
A121RID9.37*5.35*8S
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([col] [nvarchar](20))
INSERT INTO [tb]
SELECT 'P5T9*6*3C' UNION ALL
SELECT 'P5T9.53*4.75*3.2C' UNION ALL
SELECT 'W45RID9.37*5.35*8S' UNION ALL
SELECT 'W45RID9.37*5.35*8S' UNION ALL
SELECT 'A121RID9.37*5.35*8S'--SELECT * FROM [tb]-->SQL查询如下:--创建自定义函数模版
IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](
@s NVARCHAR(1000)
)RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @r NVARCHAR(100)
SET @r=LEFT(@s,PATINDEX('%[0-9]%',@s)-1)
SET @s=STUFF(@s,1,PATINDEX('%[0-9]%',@s)-1,'')
RETURN @r+LEFT(@s,PATINDEX('%[^0-9]%',@s)-1)
END
GOSELECT dbo.fn_test(col) col FROM tb
/*
col
----------------------------------------------------------------------------------------------------
P5
P5
W45
W45
A121(5 行受影响)*/
declare @table table(zf varchar(100))insert into @table
select 'P5T9*6*3C'
union all
select 'P5T9.53*4.75*3.2C'
union all
select 'W45RID9.37*5.35*8S'
union all
select 'W45RID9.37*5.35*8S'
union all
select 'A121RID9.37*5.35*8S'
union all
select 'A121aID9.37*5.35*8S'
union all
select 'A120WID9.37*5.35*8S'Select ZF, LEFT(ZF,1) + LEFT(ZF1, WZ - 1 ) AS result --结果
from (Select ZF, ZF1, PATINDEX('%[a-z|A-Z]%',ZF1) as WZ -- 第二个字字母位置
from
(
select *,right(zf,len(zf)- 1 ) as ZF1 from @table
) AS Y
)as X
P5T9*6*3C P5
P5T9.53*4.75*3.2C P5
W45RID9.37*5.35*8S W45
W45RID9.37*5.35*8S W45
A121RID9.37*5.35*8S A121
A121aID9.37*5.35*8S A121
A120WID9.37*5.35*8S A120 我投石问路吧
不会出现PD123VN*之类的字符吧?
我现在想的是写一个函数,从第三位开始判断是否是数字。
declare @tab table(c varchar(30));
insert into @tab
SELECT 'P5T9*6*3C' UNION ALL
SELECT 'P5T9.53*4.75*3.2C' UNION ALL
SELECT 'W45RID9.37*5.35*8S' UNION ALL
SELECT 'W45RID9.37*5.35*8S' UNION ALL
SELECT 'A121RID9.37*5.35*8S';
select substring(c,1,patindex('%[0-9][A-Z]%',c)) from @tab;
/*
P5
P5
W45
W45
A121
*/
--select dbo.regex_substring(c,'^[A-Z][0-9]*',1,1) from @tab;
朋友的方法更好,这下得牢牢学会patindex函数的用法了。
create table #tb(col varchar(100))
insert into #tb
select 'P5T9*6*3C'
union all
select 'P5T9.53*4.75*3.2C'
union all
select 'W45RID9.37*5.35*8S'
union all
select 'W45RID9.37*5.35*8S'
union all
select 'A121RID9.37*5.35*8S'
union all
select 'A121aID9.37*5.35*8S'
union all
select 'A120WID9.37*5.35*8S'2 语句及结果
select substring(col,1,patindex('%[a-z|A-Z]%',right(col,len(col)-1))) col from #tbcol
P5
P5
W45
W45
A121
A121
A120