Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    8

    Unanswered: Splitting alphabets and numbers in a string

    Hi,

    I have a column in a table which looks like below.

    Column
    -------
    AA123
    D123
    AXC1
    QF23

    I need to split this value into two part, Alphabets and numeric. How to do this using SQL query.

    Note:
    My column value will not have mixed characters like A1D3,G32S,12F.
    It will always follow the ablve pattern mentioned above.

    Dinesh

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If the format is *always* the same (i.e. 2 letters followed by two numbers) then here's a simple solution
    Code:
    SELECT Left(your_field, 2) As characters
         , Right(your_field, 2) As numbers
    FROM   your_table
    If the format can change in any way then you need to let us know and provide more sample data illustrating this.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    8
    Thanks for your reply.
    But column has random pattern. But any pattern can be split into 2 parts(alpha and numbers). First part is always alphabets and second is always numbers. All the column will have alphabets and numbers. There will not be any mixed of alpha and numbers.

    Below are the example rows in my table.
    Column
    -------
    ABC123
    F12345
    QD1
    GDL411

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This should help you...
    Code:
    SELECT PatIndex('%[0-9]%', your_field)
    Returns the position of the first number in your_field.

    Combine this with SubString() and you have your solution!

    Give it a go
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •