Help needed on string function
-
Hi everyone, I'm strugling with a string function. I have a PRODUCT field and it contains 4 kind of values: a) 000000000000001234 b) 000000000000123456 c) 000000000001234567 d) 000000000123456789 I want the values to look like this: [1] if the length of PRODUCT without zeros is smaller then 7 the output is: a) 0001234 b) 0123456 [2] if the length of PRODUCT without zeros is equal to 7 then output is: c) 1234567 [3] If the lengt of PRODUCT without zeros is equal to 9 then output is: d) 123456789 The If statements [1] and [2] are taken care of with af function that I call in my statement:
ALTER FUNCTION [dbo].[LPAD]
(
-- Add the parameters for the function here
-- Test with data as nvarchar like in Staging
@SourceString nvarchar(MAX), --Varchar(MAX),
@FinalLength int,
@PadChar Char(1)
)
RETURNS nvarchar(MAX) --Varchar(MAX) --<Function_Data_Type, ,Int>WITH SCHEMABINDING
AS
BEGIN
RETURN --<@ResultVar, sysname, @Result>
(Select REPLICATE(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)
ENDSQL statement:
, CAST(RTRIM(dbo.LPAD(SUBSTRING(dbo.SD.PRODUCT, PATINDEX('%[^0]%',dbo.SD.PRODUCT+ ''), LEN(dbo.SD.PRODUCT)), 7, 0))
AS nvarchar(255)) AS PRODUCTSo if PRODUCT is smaller then 7 chars I need a zero to make a maximum length of 7 chars. The first 11 zero's need to dissapear and thats taken care of with the above function and statement. It's the third statement that gives me a headache, I also want the PRODUCT with 9 chars without any leading zero. Does anyone know how I can integrate the 3rd if please ??? Kind regards, Ambertje
-
Hi everyone, I'm strugling with a string function. I have a PRODUCT field and it contains 4 kind of values: a) 000000000000001234 b) 000000000000123456 c) 000000000001234567 d) 000000000123456789 I want the values to look like this: [1] if the length of PRODUCT without zeros is smaller then 7 the output is: a) 0001234 b) 0123456 [2] if the length of PRODUCT without zeros is equal to 7 then output is: c) 1234567 [3] If the lengt of PRODUCT without zeros is equal to 9 then output is: d) 123456789 The If statements [1] and [2] are taken care of with af function that I call in my statement:
ALTER FUNCTION [dbo].[LPAD]
(
-- Add the parameters for the function here
-- Test with data as nvarchar like in Staging
@SourceString nvarchar(MAX), --Varchar(MAX),
@FinalLength int,
@PadChar Char(1)
)
RETURNS nvarchar(MAX) --Varchar(MAX) --<Function_Data_Type, ,Int>WITH SCHEMABINDING
AS
BEGIN
RETURN --<@ResultVar, sysname, @Result>
(Select REPLICATE(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)
ENDSQL statement:
, CAST(RTRIM(dbo.LPAD(SUBSTRING(dbo.SD.PRODUCT, PATINDEX('%[^0]%',dbo.SD.PRODUCT+ ''), LEN(dbo.SD.PRODUCT)), 7, 0))
AS nvarchar(255)) AS PRODUCTSo if PRODUCT is smaller then 7 chars I need a zero to make a maximum length of 7 chars. The first 11 zero's need to dissapear and thats taken care of with the above function and statement. It's the third statement that gives me a headache, I also want the PRODUCT with 9 chars without any leading zero. Does anyone know how I can integrate the 3rd if please ??? Kind regards, Ambertje
SELECT RIGHT('0000000' + @STR, CASE WHEN LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) < 7 THEN 7 ELSE LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) END )
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
SELECT RIGHT('0000000' + @STR, CASE WHEN LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) < 7 THEN 7 ELSE LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) END )
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Hi Peter, This isn't working, I get no values at all. I think I didn't mention that I'm working with a View. I used your solution like this:
,RIGHT('0000000' + dbo.SD.PRODUCT, CASE WHEN LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) < 7 THEN
7 ELSE LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) END) -
Hi Peter, This isn't working, I get no values at all. I think I didn't mention that I'm working with a View. I used your solution like this:
,RIGHT('0000000' + dbo.SD.PRODUCT, CASE WHEN LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) < 7 THEN
7 ELSE LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) END)This one will work assuming the product codes are always numeric:
DECLARE @INPUT NVARCHAR(MAX) = 'your product code with or without leading zeros goes here'
DECLARE @TMP_INPUT INT = CAST(@INPUT AS INT)SELECT
CASE
WHEN LEN(CAST(@TMP_INPUT AS NVARCHAR)) < 7 THEN SUBSTRING(@INPUT, 1, 7)
ELSE CAST(@TMP_INPUT AS NVARCHAR)
ENDRegards, Johan
My advice is free, and you may get what you paid for.
