如011111 , 001111,000111,000011,000001,001011,000100,011001....(类型是varchar)
如何将数据前面所有的0用G代替,
但中间的0不能代替。
G11111,G1111,G111,G11,G1,G1011,G100,G11001
谢谢
如何将数据前面所有的0用G代替,
但中间的0不能代替。
G11111,G1111,G111,G11,G1,G1011,G100,G11001
谢谢
调试欢乐多
go
create table [tb]([col] varchar(6))
insert [tb]
select '011111' union all
select '001111' union all
select '000111' union all
select '000011' union all
select '000001' union all
select '001011' union all
select '000100' union all
select '011001'
goselect 'G'+ltrim(cast(col as int)) from tb
/**
-------------
G11111
G1111
G111
G11
G1
G1011
G100
G11001(8 行受影响)
**/
insert #test select '001011'
insert #test select '011001'
UPDATE #test
SET i=stuff(i,1,patindex('%[^0]%',i)-1,'G')
WHERE i like '0%'select * from #testi
--------------------
G1011
G11001(2 行受影响)
INSERT INTO #tb VALUES('011111 , 001111,000111,000011,000001,001011,000100,011001')
SELECT
string,
--UPDATE #tb SET
string = STUFF((
SELECT ',G' + CAST(x.n.value('.','int') AS varchar(10))
FROM (
SELECT ValuesXML = CAST('<root>' +
REPLACE((SELECT v = string FOR XML PATH('')),',','</v><v>') +
'</root>' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)
FOR XML PATH('')
),1,1,'') --END
FROM #tb选定UPDATE到END的部分运行即可更新。
INSERT INTO #tb VALUES('011111 , 001111,000111,000011,000001,001011,000100,011001')IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](
@s VARCHAR(1000)
)RETURNS VARCHAR(1000)
AS
BEGIN
IF RIGHT(@s,1)<>',' SET @s=@s+','
DECLARE @r VARCHAR(1000)
WHILE LEN(@s)>0
SELECT @r=isnull(@r+',','')+'G'+STUFF(LEFT(@s,CHARINDEX(',',@s)-1),1,PATINDEX('%[1-9]%',@s)-1,'')
,@s=STUFF(@s,1,CHARINDEX(',',@s),'')
RETURN @r
END
GOSELECT dbo.fn_test(string) FROM #tb
/*
G11111 ,G1111,G111,G11,G1,G1011,G100,G11001(1 行受影响)
*/
嗯,select 'G'+cast(convert(int,col) as char) from table 后来想到了