/* 转换为
100-5 0100-00005
1-998 0001-00998
12-1589 0012-01589
*/
IF EXISTS(SELECT * FROM sys.objects WHERE name='Fun_CombineTransfer')
DROP FUNCTION dbo.Fun_CombineTransfer
GO
CREATE FUNCTION Fun_CombineTransfer(@aaa NVARCHAR(20))
RETURNS NVARCHAR(20)
AS
BEGIN
DECLARE @sReturn NVARCHAR(20)=''
DECLARE @ForwardNum INT
DECLARE @AfferNum INT
DECLARE @ForwardChar NVARCHAR(20)=''
DECLARE @AfferChar NVARCHAR(20)=''
SET @ForwardNum=(SELECT CHARINDEX('-',@aaa)-1)
SET @ForwardChar=( CASE @ForwardNum WHEN 1 THEN '000'WHEN 2 THEN '00'WHEN 3 THEN '0'WHEN 4 THEN ''END)
SET @AfferNum=(SELECT CHARINDEX('-',REVERSE(@aaa))-1)
SET @AfferChar=(CASE @AfferNum WHEN 1 THEN '0000'WHEN 2 THEN '000'WHEN 3 THEN '00'WHEN 4 THEN '0'END)
SET @sReturn=@ForwardChar+(SELECT SUBSTRING(@aaa,1,CHARINDEX('-',@aaa)))+@AfferChar+
(SELECT REVERSE(SUBSTRING(REVERSE(@aaa),1,(CHARINDEX('-',REVERSE(@aaa))-1))))
RETURN @sReturn
END
第二种方式
UPDATE #temp SET aaa=RIGHT('000'+SUBSTRING(aaa,1,CHARINDEX('-',aaa)-1),4)+'-'+
RIGHT('0000'+SUBSTRING(aaa,CHARINDEX('-',aaa)+1,LEN(aaa)),5)