Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unhappy Unanswered: How to fetch only first 6 numeric values from varchar column

    Someone please help to write query for below screnaio.
    I want to fetch only first 6 numeric values from varchar column using below conditions.
    Conditions are,
    1. Substring only first 6 numeric.
    2. If any substring output has alphabet ignore the alphabets.

    Tablename: Sys_tablename
    Columnname: Sys_columnname

    Select Sys_columnname from Sys_tablename;
    Go

    Sys_columnname:
    ================
    1234564567890
    ABCDEFGHIJKLMNOPQRSTU
    123456AABCDEFGH
    123ABCDEFGHIJKL12
    12345ASDFASDFEA123
    123456789ASDFASDASDF
    12345SDFASDFASDF123

    Output would be like below:
    ======================
    123456
    NULL (ignore)
    123456
    123
    12345
    123456
    12345

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Can I ask why you're doing this or is it just coursework?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    c'mon MBK, its easy, they are storing multiple entities in the same column and not sure that they should fix it or not.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I was going to wait for a reply before lecturing them on their design (or their table naming). Looking at the data though I'd guess it's coursework.

    MBK

  5. #5
    Join Date
    Nov 2002
    Posts
    21
    You can do like this(Only digit and alp).

    Select case patindex('%[A-Z]%',upper(Sys_columnname))>6 then substring(Sys_columnname,1,6)
    else
    substring(Sys_columnname,1,patindex('%[A-Z]%',upper(Sys_columnname))) from Sys_tablename

  6. #6
    Join Date
    Aug 2010
    Posts
    4
    Quote Originally Posted by mike_bike_kite View Post
    I was going to wait for a reply before lecturing them on their design (or their table naming). Looking at the data though I'd guess it's coursework.

    MBK
    Hi MBK,
    I got this doubt,as part of self learning execise on SYBASE database. If u have any idea on this kindly share with me.

  7. #7
    Join Date
    Aug 2010
    Posts
    4
    thanks hobblylu.

    But patindex is not working.

  8. #8
    Join Date
    Nov 2002
    Posts
    21
    Sorry for upper sql. It's wrong.
    Right SQL is:
    Select upper(Sys_columnname),case when patindex('%[A-Z]%',upper(Sys_columnname))>6 then substring(Sys_columnname,1,6)
    when patindex('%[A-Z]%',upper(Sys_columnname))=0 then substring(Sys_columnname,1,6)
    else
    substring(Sys_columnname,1,patindex('%[A-Z]%',upper(Sys_columnname))-1) end from Sys_tablename
    1234564567890
    123456
    ABCDEFGHIJKLMNOPQRSTU
    NULL
    123456AABCDEFGH
    123456
    123ABCDEFGHIJKL12
    123
    12345ASDFASDFEA123
    12345
    123456789ASDFASDASDF
    123456
    12345SDFASDFASDF123
    12345

  9. #9
    Join Date
    Aug 2010
    Posts
    4
    thanks a lot hobblylu.Its working ....

Posting Permissions

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