SQL - extracing digits from a number
 

Is there a way to extract digits from a number using a SQL statement? I want
to extract the first digit, second digit etc. from a number. I am using SQL
on an Excel table. In VBA I can do it by casting a number as text and then
pulling the digits out of the character string.

Thanks and Happy New Year,

Dave

SQL - extracing digits from a number
 

Against an Excel table using JET SQL I think the MID function should
work:
"Select mid(field_name,1,1) as CharacterOne from table_name" would pull
the first character.

In other flavors of SQL the SUBSTR function would do the same thing:
Select SUBSTR((field_name,1,1) as CharacterOne from table_name

SQL - extracing digits from a number
 

That is it! Thanks for the help. It works exactly as I need it.

Dave


"Buck" wrote:

Against an Excel table using JET SQL I think the MID function should
work:
"Select mid(field_name,1,1) as CharacterOne from table_name" would pull
the first character.

In other flavors of SQL the SUBSTR function would do the same thing:
Select SUBSTR((field_name,1,1) as CharacterOne from table_name

SQL - extracing digits from a number
 

Buck wrote:
In other flavors of SQL the SUBSTR function would do the same thing

AFAIK that 'flavor' is proprietary to Oracle. The ANSI standard
equivalent is SUBSTRING.

Jamie.

--

SQL - extracing digits from a number
 

Jamie: You are probably correct. I work with Microsot, db2, and Oracle
databases and occassionally I forget what works with what.