Rookie Prolem: Using DIGITS() function on an INTEGER value
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
June 23, 2008 2:23 PM
UPDATED:
July 7, 2008 4:55 PM
RELATED QUESTIONS
- SQL update command to update certain digits in a row of a column with VARCHAR2(9) DATATYPE.
- PATINDEX Instruction Result Difference between SQL 7 and SQL 2000.
- AS/400 - iSeries SQL Update of on table with values from another joined table
- How to use 'Create index' statement in iSeries Navigator
- How to convert LONG to CLOB or to VARCHAR type in select statement
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
What platform is your database?
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.