• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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

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
bryan_z
Asked:
bryan_z
  • 2
  • 2
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Are the letter portions always at the front?
0
 
bryan_zAuthor Commented:
Yes, the letters are always at the front.
0
 
pssandhuCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Kevin CrossChief Technology OfficerCommented:
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
 
pssandhuCommented:
yes, so if the numbers are always in the begining then mwvisa1's solution is better.
P.
0
 
bryan_zAuthor Commented:
Thanks, this was very helpful and its great to know about the patterns.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now