Working with variable length number

Question

Post reply

Working with variable length number

Jonathan AC Roberts

SSCoach

Points: 18263

January 3, 2020 at 3:20 pm

Answer

#3712154

oogibah wrote:

Hey Jon,
I troll these forums looking at other peoples issues so I can have a better understanding when I run into issues of my own.. That being said I'm trying to understand how this query works, is it that when you flip the number and convert it to bigint the conversion truncates the leading 0s?

Yes that's how it works.

Another way to do it would be to replace all the zeros with a space, trim the string, then replace any spaces with zeros (for any zeros in the middle of the number):

;with cte as
(
    select *
      from (values ('1230000045'),
                  ('120370000068'),
                  ('12340500080076'),                  
                  ('120300080025')) T(AcctNumber)
)
select cte.AcctNumber,x.firstDigits,x.lastDigits,x.firstDigits+x.lastDigits allDigits
  from cte
 cross apply(values (right(cte.AcctNumber,4),
                     replace(rtrim(replace(left(cte.AcctNumber,len(cte.AcctNumber)-5),'0',' ')),' ','0')
                    )
            ) x(lastDigits,firstDigits)

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

GaryV Hall of Fame Points: 3307 More actions · Answer 1

What are the rules for which digits should be kept and which discarded?

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

the rules are you take the last 4 digits and the preceeding numbers before the string of zeros. so if account is 12340500080076 , I need 123405 concatenated with 0076 to make account 1234050076. the problem is that I do not have a say on how the data is populated and its coming from two different systems, in one system the account number is padded with extra zeros and in some instances there is an extra digit like 8 on my example which is used by that system as an identifier for the source system. while on the other system the extra padding and the extra digit is not included. Hence the need to take the last 4 digits and the preceeding numbers before the string of zeros

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

;with cte as
(
    select *
      from (values ('1230000045'),
                  ('120370000068'),
                  ('12340500080076'),                  
                  ('120300080025')) T(AcctNumber)
)
select cte.AcctNumber,x.firstDigits,x.lastDigits,x.firstDigits+x.lastDigits allDigits
  from cte
 cross apply(values (right(cte.AcctNumber,4),
                     reverse(convert(bigint,(right(reverse(cte.AcctNumber),len(cte.AcctNumber)-5))))
                    )
            ) x(lastDigits,firstDigits)

PS: you can't have any of the first digits ending with a zero.

oogibah SSC Eights! Points: 827 More actions · Answer 4

Hey Jon,

I troll these forums looking at other peoples issues so I can have a better understanding when I run into issues of my own.. That being said I'm trying to understand how this query works, is it that when you flip the number and convert it to bigint the conversion truncates the leading 0s?

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 5

As the OP is using SQL 2017 there is an even easier way to get the results:

;with cte as
(
    select *
      from (values ('1230000045'),
                  ('120370000068'),
                  ('12340500080076'),                  
                  ('120300080025')) T(AcctNumber)
)
select cte.AcctNumber,x.firstDigits,x.lastDigits,x.firstDigits+x.lastDigits allDigits
  from cte
 cross apply(values (right(cte.AcctNumber,4),
                     trim('0' FROM (left(cte.AcctNumber,len(cte.AcctNumber)-5)))
                    )
            ) x(lastDigits,firstDigits)

Trybbe Hall of Fame Points: 3967 More actions · Answer 6

Sorry for the late response guys. Jonathan, this is exactly what I needed thank you very much