0

I receive UPC data from a customer as 13 digits but I only use the right 10 digits so I am trying to use Right(upc1,10) to get the data I want. I am doing something wrong because I am only getting 7 digits.

select div,   upc1,   right(upc1,10) as upc,   len(upc1),   
       len(right(upc1,10))
from Custfile

div   upc1            upc       (No column name)    (No column name)
10    1001111090729   1090729       13              7
10    1004139005004   9005004       13              7

Since I was only getting 7, I tried asking for Right 13 instead of Right 7 and am getting 10 digits

select div, upc1, right(upc1,13) as upc, len(upc1), 
       len(right(upc1,13))
from Custfile

div   upc1            upc        (No column name)   (No column name)
10    1001111090729   1111090729    13             10

This give me what I want but I am sure I am missing something obvious. Would appreciate if someone could point out my error.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Jim O
  • 1
  • (1) Tag your question with the database you are using. (2) What is the type of `upc1`? – Gordon Linoff Apr 06 '20 at 00:44
  • What is returned when Select '-->' + upc1 + '<--' ? Is upc1 char or varchar? What type is it? – donPablo Apr 06 '20 at 00:56
  • This is clearly documented by Microsoft in MSDN at http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx, which states LEN "returns the number of characters of the specified string expression, excluding trailing blanks". See https://stackoverflow.com/questions/2025585/len-function-not-including-trailing-spaces-in-sql-server – donPablo Apr 06 '20 at 00:59

1 Answers1

1

You would appear to have three invisible characters at the end of the UPC. If these a trailing normal spaces, then trim() might help or replace(). That would look like:

right(replace(upc1, ' ', ''), 10)

This assumes the problem is trailing spaces.

Alternatively, use substr() or your database's equivalent:

substr(upc1, 4, 10)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon Linoff and donPablo for your help. There was something there beyond 13 digits that I could not see. – Jim O Apr 06 '20 at 16:25