sql query needed

  • basically i need a query to fetch serial number and letter from the column which has two type of data

    12345 0047225P1

    00011 0055500ABC

    The first data should have serial number = 004722 and letter = P

    The second data should have serial number = 0055500AB and letter = C

    Thanks

  • You're not telling much about your data.

    Can you post the table script, some sample data and the expected results based on the sample data?

    Also, what makes P the letter in the first row and C in the last row?

    Is there a logic that can be implemented (eg. last non numeric char in the string?)

    -- Gianluca Sartori

  • create table abc

    (

    SerialNumber varchar (50)

    )

    insert into abc (SerialNumber)values ('12345 0047225P1')

    insert into abc (SerialNumber)values ('00011 0055500ABC')

    I am expecting a table with once column name account number and the result like

    Account Number

    P 0047225

    C 0055500

    I hope this answers your questiong

  • Based on what you provided so far, this could be one solution:

    SELECT '004722' AS [serial number] , 'P' AS letter

    UNION ALL

    SELECT '0055500AB' AS [serial number] , 'C' AS letter

    If that's not what you're looking for, please provide more details as described in the first link in my signature.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • mario.balatellii (12/30/2011)


    create table abc

    (

    SerialNumber varchar (50)

    )

    insert into abc (SerialNumber)values ('12345 0047225P1')

    insert into abc (SerialNumber)values ('00011 0055500ABC')

    I am expecting a table with once column name account number and the result like

    Account Number

    P 0047225

    C 0055500

    I hope this answers your questiong

    What makes P the letter for the first row and C for the last row?

    -- Gianluca Sartori

  • Didn't see your latest reply, I'm sorry...

    What is the logic to get "P" based on "0047225P1"? Shouldn't it be "1"?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks for your replies

    i am looking for something like this

    DECLARE @STR VARCHAR(100)

    SET @STR = '12345 0047225P1'

    SELECT SUBSTRING(@Str,CHARINDEX(' ',@Str)+8,1) + ' '+ SUBSTRING(@Str,CHARINDEX(' ',@Str)+1,7) AccountNo

    I thot the data in the column serious number will always be like 12345 0047225P1

    but there is one more patern like 00011 0055500ABC. So if i knew the logic my self i would have done it .

  • mario.balatellii (12/30/2011)


    So if i knew the logic my self i would have done it .

    I guess you will have to ask that to somebody who knows the business logic.

    I don't think we can help here.

    -- Gianluca Sartori

  • Ok i analysed the data and got a clue

    create table abc

    (

    SerialNumber varchar (50)

    )

    insert into abc (SerialNumber)values ('12345 0047225P1')

    insert into abc (SerialNumber)values ('00011 0055500ABC2')

    So basically there is the letter to choose should precede a number

    like in first data P is before 1

    and second data C is before 2

    any help?

  • However it's late to point it but there are two threads running for same issue in SSC. Both have more than 5 replies. 😀

    This is the early thread.

    http://www.sqlservercentral.com/Forums/Topic1227680-392-1.aspx

  • Thanx, Dev.

    At least I didn't waste too much time on it...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • DECLARE @abc TABLE

    (

    SerialNumber varchar (50)

    )

    insert into @abc (SerialNumber)values ('12345 0047225P1')

    insert into @abc (SerialNumber)values ('00011 0055500ABC2')

    SELECT

    SUBSTRING(SerialNumber, PATINDEX('% %', SerialNumber) + 1, PATINDEX('%[^0-9][0-9]', SerialNumber) - PATINDEX('% %', SerialNumber) - 1) AS SerialNumber,

    SUBSTRING(SerialNumber, PATINDEX('%[^0-9][0-9]', SerialNumber) , 1) AS Letter

    FROM @abc

    -- Gianluca Sartori

  • thank you very much Sir!

  • Good Morning...

    DECLARE @abc TABLE

    (

    SerialNumber varchar (50)

    )

    insert into @abc (SerialNumber)values ('00132 0045749WSE1')

    insert into @abc (SerialNumber)values ('00181 0102670N1')

    I want to get the account number and the First Alphabet after the account number

    Something like

    Account Number Account Letter

    0045749 W

    0102670 N

    Regards

  • SELECT

    SUBSTRING(SerialNumber, PATINDEX('% %', SerialNumber) + 1, PATINDEX('%[^0-9 ]%', SerialNumber) - PATINDEX('% %', SerialNumber) -1) AS SerialNumber,

    SUBSTRING(SerialNumber, PATINDEX('%[^0-9 ]%', SerialNumber) , 1) AS Letter

    FROM @abc

    -- Gianluca Sartori

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply