June 28, 2019 at 10:16 am
Hi there
I have the following code which has to extract an number from a AccountNumber variable
declare @AccountNumber varchar(20) = '8063#0018375'
select SUBSTRING(@AccountNumber,CHARINDEX('#',@AccountNumber)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber))
select Len(SUBSTRING(@AccountNumber,CHARINDEX('#',@AccountNumber)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber))) as FinalAccountNumber
I need to ensure that Final Account Number is padded to 7 characters because i could have the following:
declare @AccountNumber2 varchar(20) = '8063#018375'
select SUBSTRING(@AccountNumber2,CHARINDEX('#',@AccountNumber2)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber2))
select Len(SUBSTRING(@AccountNumber2,CHARINDEX('#',@AccountNumber2)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber2))) as FinalAccountNumber
Where the length of Final Account Number is 6 characters
How can I ensure that Final Account Numbers will always be padded to 7 characters using leading zeros
June 28, 2019 at 10:41 am
You can add padding to the start/end of the value, then use left/right to get the required length
DECLARE @AccountNumber2 varchar(20) = '8063#018375';
DECLARE @PaddingChar char(1) = 'x'; -- Change your padding character here
DECLARE @RequiredLen int = 7; -- Change the padded length here
SELECT FinalAccountNumber = SUBSTRING( @AccountNumber2, CHARINDEX( '#', @AccountNumber2 ) + 1, LEN( @AccountNumber2 ) - CHARINDEX( '#', @AccountNumber2 ) )
, LeftPaddedFinalAccountNumber = RIGHT(REPLICATE( @PaddingChar, @RequiredLen ) + SUBSTRING( @AccountNumber2, CHARINDEX( '#', @AccountNumber2 ) + 1, LEN( @AccountNumber2 ) - CHARINDEX( '#', @AccountNumber2 )), @RequiredLen)
, RightPaddedFinalAccountNumber = LEFT(SUBSTRING( @AccountNumber2, CHARINDEX( '#', @AccountNumber2 ) + 1, LEN( @AccountNumber2 ) - CHARINDEX( '#', @AccountNumber2 )) + REPLICATE( @PaddingChar, @RequiredLen ), @RequiredLen);
June 28, 2019 at 1:04 pm
Thanks Des
If i wanted to extract the left part of this (8063) and pad to 4 characters then how i would change the above please?
June 28, 2019 at 1:33 pm
Hi there I have the following code which has to extract an number from a AccountNumber variable declare @AccountNumber varchar(20) = '8063#0018375' select SUBSTRING(@AccountNumber,CHARINDEX('#',@AccountNumber)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber)) select Len(SUBSTRING(@AccountNumber,CHARINDEX('#',@AccountNumber)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber))) as FinalAccountNumber I need to ensure that Final Account Number is padded to 7 characters because i could have the following: declare @AccountNumber2 varchar(20) = '8063#018375' select SUBSTRING(@AccountNumber2,CHARINDEX('#',@AccountNumber2)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber2)) select Len(SUBSTRING(@AccountNumber2,CHARINDEX('#',@AccountNumber2)+1,Len(@AccountNumber) - CHARINDEX('#',@AccountNumber2))) as FinalAccountNumber Where the length of Final Account Number is 6 characters How can I ensure that Final Account Numbers will always be padded to 7 characters using leading zeros
How many rows per batch are you going to need to do this for?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2019 at 1:55 pm
WITH myTable AS (SELECT * FROM (VALUES ('3#5'),('8063#0018375'),('8063#018375'),('063#018375'),('063invalid018375')) X(AccountNumber))
SELECT myTable.AccountNumber,
RIGHT('0000'+LEFT(myTable.AccountNumber,CHARINDEX('#',myTable.AccountNumber)-1),4) PaddedLeftAccountNumber,
RIGHT('0000000'+REVERSE(LEFT(T.RevAccountNumber,CHARINDEX('#', T.RevAccountNumber)-1)),7) PaddedRightAccountNumber
FROM myTable
CROSS APPLY(VALUES (REVERSE(myTable.AccountNumber))) T(RevAccountNumber)
WHERE myTable.AccountNumber LIKE '%#%'June 28, 2019 at 3:25 pm
You can do this easily with string functions, but it’s still a stinking kludge caused by bad design. Most of the work in SQL is done in the DDL, and not include use in the DML like you’re trying to do. If your account number is truly seven digits, then follow “Brent’s Rule” which says that you store data the way you use it and you use it the way it’s stored.
CREATE TABLE Accounts
(acct_nbr CHAR(7) NOT NULL PRIMARY KEY
CHECK(acct_nbr LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9]’),
...);
Multiple data elements should never be embedded in each other -- ever hear of First Normal Form (1NF)?
Please post DDL and follow ANSI/ISO standards when asking for help.
June 28, 2019 at 3:29 pm
;WITH myTable AS (SELECT * FROM (VALUES ('3#5'),('8063#0018375'),('8063#018375'),('063#018375'),('063invalid018375')) X(AccountNumber))
SELECT
t.AccountNumber,
lrp.LeftMostAccountNumberPart,
lrp.RightMostAccountNumberPart,
fp.AdjustedAccountID
FROM myTable t
CROSS APPLY (
SELECT
CHARINDEX('#', t.AccountNumber) AS sepPos,
LEN(t.AccountNumber) AS accLen
) sp
CROSS APPLY (
SELECT
RIGHT('0000' + LEFT(t.AccountNumber,sp.sepPos-1),4) AS LeftMostAccountNumberPart,
RIGHT('0000000' + RIGHT(t.AccountNumber,sp.accLen-sp.sepPos),7) AS RightMostAccountNumberPart
) lrp
CROSS APPLY (
SELECT
lrp.LeftMostAccountNumberPart + '#' + lrp.RightMostAccountNumberPart AS AdjustedAccountID
) fp
WHERE t.AccountNumber LIKE '%#%'This is just a slightly different version that use CROSS APPLY to produce intermediate calculations - which might make it easier to follow along (or maybe not).... 🙂
June 28, 2019 at 3:52 pm
;WITH myTable AS (SELECT * FROM (VALUES ('3#5'),('8063#0018375'),('8063#018375'),('063#018375'),('063invalid018375')) X(AccountNumber))
SELECT
t.AccountNumber,
lrp.LeftMostAccountNumberPart,
lrp.RightMostAccountNumberPart,
fp.AdjustedAccountID
FROM myTable t
CROSS APPLY (
SELECT
CHARINDEX('#', t.AccountNumber) AS sepPos,
LEN(t.AccountNumber) AS accLen
) sp
CROSS APPLY (
SELECT
RIGHT('0000' + LEFT(t.AccountNumber,sp.sepPos-1),4) AS LeftMostAccountNumberPart,
RIGHT('0000000' + RIGHT(t.AccountNumber,sp.accLen-sp.sepPos),7) AS RightMostAccountNumberPart
) lrp
CROSS APPLY (
SELECT
lrp.LeftMostAccountNumberPart + '#' + lrp.RightMostAccountNumberPart AS AdjustedAccountID
) fp
WHERE t.AccountNumber LIKE '%#%'
June 28, 2019 at 4:02 pm
One must apparently be very careful with certain words here. I posted a reply which I subsequently edited to include a comment about me a busing (one word) CROSS APPLY to produce intermediate results, and suddenly the post was removed because it was considered SPAM. It could still be edited and updated, but even after I paraphased it, the post still remains marked as SPAM.
I assume it's because of some automatic, not very intelligent, code is monitoring the content and has found that that word is indicative of SPAM and removed the post on behalf of the editor, not to look at it again.
June 28, 2019 at 7:20 pm
;WITH SampleData AS (
SELECT * FROM (VALUES (''),('3#5'),('8063#0018375'),('8063#018375'),
('063#018375'),('063invalid018375')) Data(AccountNumber)
)
SELECT AccountNumber, RIGHT('000000' + SUBSTRING(AccountNumber,
NULLIF(CHARINDEX('#', AccountNumber), 0) + 1, 10), 7) AS FinalAccountNumber
FROM SampleData
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 28, 2019 at 9:00 pm
You can do this easily with string functions, but it’s still a stinking kludge caused by bad design. Most of the work in SQL is done in the DDL, and not include use in the DML like you’re trying to do. If your account number is truly seven digits, then follow “Brent’s Rule” which says that you store data the way you use it and you use it the way it’s stored. CREATE TABLE Accounts (acct_nbr CHAR(7) NOT NULL PRIMARY KEY CHECK(acct_nbr LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9]’), ...); Multiple data elements should never be embedded in each other -- ever hear of First Normal Form (1NF)?
Please post a link for the "Brent's Rule" that you've cited.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2019 at 9:31 pm
jcelko212 32090 wrote:You can do this easily with string functions, but it’s still a stinking kludge caused by bad design. Most of the work in SQL is done in the DDL, and not include use in the DML like you’re trying to do. If your account number is truly seven digits, then follow “Brent’s Rule” which says that you store data the way you use it and you use it the way it’s stored. CREATE TABLE Accounts (acct_nbr CHAR(7) NOT NULL PRIMARY KEY CHECK(acct_nbr LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9]’), ...); Multiple data elements should never be embedded in each other -- ever hear of First Normal Form (1NF)?
Please post a link for the "Brent's Rule" that you've cited.
Presumably Brent Ozar? WAG, I guess, but often on his pages he'll include a "Brent's rule".
Isn't the way data is stored irrelevant to how it's used/displayed!? Dates nor times, for example, are stored in any format used or easily recognized by humans.
No q that 1NF should be followed, and that this doesn't. But that's hardly unique among systems, however undesirable the practice is.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 30, 2019 at 5:38 pm
Brent's point is that temporal data should be kept in a temporal data type, numeric data in numeric data types, etc. And not those insane kludges we see where someone is using a BIGINT for a date, floating point for decimal values, strings for everything, etc. His rule is for the database tier, not the presentation layers. I refer to columns with multiple data elements in them that have to be split out as "automobiles, squids and Lady Gaga" columns. If they had a valid descriptive name, then it would look like that.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 1, 2019 at 6:09 am
Use this pattern to pad your values
DECLARE @PaddingChar char(1) = 'x'; -- Change your padding character here
DECLARE @RequiredLen int = 7; -- Change the padded length here
SELECT LeftPaddedFinalAccountNumber = RIGHT(REPLICATE( @PaddingChar, @RequiredLen ) + [[YourValueToBePaddedGoesHere]], @RequiredLen)
, RightPaddedFinalAccountNumber = LEFT([[YourValueToBePaddedGoesHere]] + REPLICATE( @PaddingChar, @RequiredLen ), @RequiredLen);
You could even create a function to do it for you
CREATE FUNCTION dbo.PadValue (
@ValueToPad nvarchar(1000) -- This is the initial value to be padded
, @PaddingChar nchar(1) -- This is the character to use for padding
, @RequiredLen int -- This is the final length of the padded value
, @AllowTruncate bit -- If 1, this will LEFT|RIGHT trim @ValueToPad if it is longer than @RequiredLen
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT LeftPaddedValue = CASE WHEN LEN(@ValueToPad) >= @RequiredLen AND @AllowTruncate = 0 THEN @ValueToPad
ELSE RIGHT(REPLICATE( @PaddingChar, @RequiredLen ) + @ValueToPad, @RequiredLen)
END
, RightPaddedValue = CASE WHEN LEN(@ValueToPad) >= @RequiredLen AND @AllowTruncate = 0 THEN @ValueToPad
ELSE LEFT(@ValueToPad + REPLICATE( @PaddingChar, @RequiredLen ), @RequiredLen)
END;
GO
Use the function to pad the value of a variable
DECLARE @AccountNumber2 varchar(20) = '8063#018375';
DECLARE @FinalAccountNumber varchar(20) = SUBSTRING( @AccountNumber2, CHARINDEX( '#', @AccountNumber2 ) + 1, LEN( @AccountNumber2 ) - CHARINDEX( '#', @AccountNumber2 ) )
SELECT LeftPaddedValue, RightPaddedValue
FROM dbo.PadValue(@FinalAccountNumber, 'x', 7, 0)
Use the function to pad the values from a table
INSERT INTO #TestData ( AccountNumber2 )
VALUES ( '8063#018375' )
, ( '8564#18375' )
, ( '9067#8375' )
, ( '9568#75' );
GO
SELECT td.AccountNumber2, pv.LeftPaddedValue, pv.RightPaddedValue
FROM #TestData AS td
CROSS APPLY dbo.PadValue(SUBSTRING( td.AccountNumber2, CHARINDEX( '#', td.AccountNumber2 ) + 1, LEN( td.AccountNumber2 ) - CHARINDEX( '#', td.AccountNumber2 ) ), 'x', 7, 0) AS pv
SELECT td.AccountNumber2, pv.LeftPaddedValue, pv.RightPaddedValue
FROM #TestData AS td
CROSS APPLY dbo.PadValue(SUBSTRING( td.AccountNumber2, CHARINDEX( '#', td.AccountNumber2 ) + 1, LEN( td.AccountNumber2 ) - CHARINDEX( '#', td.AccountNumber2 ) ), 'x', 5, 1) AS pv
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply