如何向我的数据中添加前导零？
如何向我的数据中添加前导零？
提问于 2015-03-10 18:32:56
我有一个varchar列，它包含以下数据的变体：WKKT-FM, 2/21 WKKT-FM, 1/24-2/14 WKKT-FM, 3/14-3/21, 4/11-4/18 IKKT-FM, 12/29-1/12, 1/26-2/09, 6/01-6/15
诸若此类。我需要在几天或几个月前放置领先的零。正如你所看到的，其中一些已经有前导零。
我试着寻找#/或-#，但我必须说明一些已经具有前导零的值。
函数更好，因为我必须通过SELECT语句获得这个字段。
什么是实现这一目标的有效方法？
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-03-10 18:44:47
这是丑陋的，但它应该有效。
CREATE FUNCTION udf_AddMissingZero
(
@val NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SELECT @val = @val + ' ',
@val = REPLACE(@val, ' 1/', ' 01/'),
@val = REPLACE(@val, ' 2/', ' 02/'),
@val = REPLACE(@val, ' 3/', ' 03/'),
@val = REPLACE(@val, ' 4/', ' 04/'),
@val = REPLACE(@val, ' 5/', ' 05/'),
@val = REPLACE(@val, ' 6/', ' 06/'),
@val = REPLACE(@val, ' 7/', ' 07/'),
@val = REPLACE(@val, ' 8/', ' 08/'),
@val = REPLACE(@val, ' 9/', ' 09/'),
@val = REPLACE(@val, ' 1-', ' 01-'),
@val = REPLACE(@val, ' 2-', ' 02-'),
@val = REPLACE(@val, ' 3-', ' 03-'),
@val = REPLACE(@val, ' 4-', ' 04-'),
@val = REPLACE(@val, ' 5-', ' 05-'),
@val = REPLACE(@val, ' 6-', ' 06-'),
@val = REPLACE(@val, ' 7-', ' 07-'),
@val = REPLACE(@val, ' 8-', ' 08-'),
@val = REPLACE(@val, ' 9-', ' 09-'),
@val = REPLACE(@val, '-1/', '-01/'),
@val = REPLACE(@val, '-2/', '-02/'),
@val = REPLACE(@val, '-3/', '-03/'),
@val = REPLACE(@val, '-4/', '-04/'),
@val = REPLACE(@val, '-5/', '-05/'),
@val = REPLACE(@val, '-6/', '-06/'),
@val = REPLACE(@val, '-7/', '-07/'),
@val = REPLACE(@val, '-8/', '-08/'),
@val = REPLACE(@val, '-9/', '-09/'),
@val = REPLACE(@val, '-1-', '-01-'),
@val = REPLACE(@val, '-2-', '-02-'),
@val = REPLACE(@val, '-3-', '-03-'),
@val = REPLACE(@val, '-4-', '-04-'),
@val = REPLACE(@val, '-5-', '-05-'),
@val = REPLACE(@val, '-6-', '-06-'),
@val = REPLACE(@val, '-7-', '-07-'),
@val = REPLACE(@val, '-8-', '-08-'),
@val = REPLACE(@val, '-9-', '-09-'),
@val = REPLACE(@val, '/1/', '/01/'),
@val = REPLACE(@val, '/2/', '/02/'),
@val = REPLACE(@val, '/3/', '/03/'),
@val = REPLACE(@val, '/4/', '/04/'),
@val = REPLACE(@val, '/5/', '/05/'),
@val = REPLACE(@val, '/6/', '/06/'),
@val = REPLACE(@val, '/7/', '/07/'),
@val = REPLACE(@val, '/8/', '/08/'),
@val = REPLACE(@val, '/9/', '/09/'),
@val = REPLACE(@val, '/1-', '/01-'),
@val = REPLACE(@val, '/2-', '/02-'),
@val = REPLACE(@val, '/3-', '/03-'),
@val = REPLACE(@val, '/4-', '/04-'),
@val = REPLACE(@val, '/5-', '/05-'),
@val = REPLACE(@val, '/6-', '/06-'),
@val = REPLACE(@val, '/7-', '/07-'),
@val = REPLACE(@val, '/8-', '/08-'),
@val = REPLACE(@val, '/9-', '/09-'),
@val = REPLACE(@val, '/1 ', '/01 '),
@val = REPLACE(@val, '/2 ', '/02 '),
@val = REPLACE(@val, '/3 ', '/03 '),
@val = REPLACE(@val, '/4 ', '/04 '),
@val = REPLACE(@val, '/5 ', '/05 '),
@val = REPLACE(@val, '/6 ', '/06 '),
@val = REPLACE(@val, '/7 ', '/07 '),
@val = REPLACE(@val, '/8 ', '/08 '),
@val = REPLACE(@val, '/9 ', '/09 ')
RETURN Rtrim(@val)
END
GOStack Overflow用户
发布于 2015-03-10 18:53:02
下面是一个函数，它将接受一个字符串，并返回带有左填充零的单数数字
CREATE FUNCTION PadNumberLeft (@WrkInfo VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @OutputData VARCHAR(25) = ''
DECLARE @iCount INT = 1
WHILE @iCount <= LEN(@WrkInfo)
BEGIN
IF ISNUMERIC(SUBSTRING(@WrkInfo, @iCount, 1)) = 1 AND
ISNUMERIC(SUBSTRING(@WrkInfo, @iCount + 1, 1)) = 1
BEGIN
SET @OutputData = @OutputData + SUBSTRING(@WrkInfo, @iCount, 3)
SET @iCount = @iCount + 3
END
IF ISNUMERIC(SUBSTRING(@WrkInfo, @iCount, 1)) = 1 AND
ISNUMERIC(SUBSTRING(@WrkInfo, @iCount + 1, 1)) = 0
BEGIN
SET @OutputData = @OutputData + '0' + SUBSTRING(@WrkInfo, @iCount, 2)
SET @iCount = @iCount + 1
END
SET @iCount = @iCount + 1
END
RETURN @OutputData
END;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28971479
复制相关文章
相似问题
腾讯云开发者
