Rookie Prolem: Using DIGITS() function on an INTEGER value

105 pts.
Tags:
SQL
SQL statements
Folk, here's my problem: My SQL knowledge is weak, but I know the basics based on RDBMS work. I have an SQL statement that contains the following statement: SUBSTR(DIGITS(b.SIN),2,9) My output looks like this: ".&114172661" (Quotes added for clarity. The SIN is a 9 digit INTEGER (114172661). It's the first two 'characters' (".&") that have me stumped. I'm thinking of replacing the SUBSTR(DIGITS(b.SIN),2,9) with CHAR(b.SIN,2,9). Would that be correct? Any suggestions on how to fix this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Jonty,
I’m using DB2 and I tried this:
SELECT SUBSTR(DIGITS(123456789),2,9) FROM SYSIBM.SYSDUMMY1
this returned :
COL1
———
123456789

If you try this with CHAR, you’ll need to change your starting character address to 1 because character strings are left justified.

When I try :
SELECT SUBSTR(CHAR(123456789),2,9) FROM SYSIBM.SYSDUMMY1
I get:
COL1
———
23456789
using SELECT SUBSTR(CHAR(123456789),1,9) FROM SYSIBM.SYSDUMMY1
returns the desired result.
COL1
———
123456789

Your .& is not a product of the SQL. It is coming from one of the following:
your DB engine, your middleware, your editor or something unique to your environment.

I hope this helps.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    What platform is your database?
    66,130 pointsBadges:
    report
  • Jonty Walker
    Thanks for that info. It turns out that the SQL query was the culprit. The CHAR function forced the SQL into a 80 character length, while the original query was pushing the length outside of the 80 limit - hence the extra charaters (FB vs VB). Much oblidged to all who have assisted.
    105 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following