Solved

In SQL, how can I separate characters from numeric values in a field?

Posted on 2009-07-10
6
286 Views
Last Modified: 2012-06-22
I have a column called "invoice_identifiers" that has data as follows:

123456
AB123456
A123456

What is need to write a SQL statement that

1) separates the alpha from the numeric
2) places them into individual fields

For example, the numeric part would go into a field invoice_number and the alpha part would go into invoice_letters.

Any help and examples are greatly appreciated.
0
Comment
Question by:bryan_z
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24826846
Are the letter portions always at the front?
0
 

Author Comment

by:bryan_z
ID: 24826874
Yes, the letters are always at the front.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24826891
You can create a funtion whcih will retrun the results. Here is an example whihc will extract all the numbers from a given string:
Declare @s varchar(100),@result varchar(100)set @s='as4khd0939sdf78' 
set @result=''
 
select
    @result=@result+case when number like '[0-9]' then number else '' end from 
    (
         select substring(@s,number,1) as number from 
        (
            select number from master..spt_values where type='p' and number between 1 and len(@s)
        ) as t
    ) as t 
select @result as only_numbers 

Open in new window

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 24826897
Try like this:
select left(invoice_identifiers, patindex('%[0-9]%', invoice_identifiers)-1) as invoice_letters
, substring(invoice_identifiers, patindex('%[0-9]%', invoice_identifiers), len(invoice_identifiers)) as invoice_numbers
from your_table_name

Open in new window

0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24826931
yes, so if the numbers are always in the begining then mwvisa1's solution is better.
P.
0
 

Author Closing Comment

by:bryan_z
ID: 31602248
Thanks, this was very helpful and its great to know about the patterns.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question