Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    15

    Unanswered: Returning partial (substring) of column value

    I am sure there is an easy way of doing this via SQL though could use some help.

    Here is my scenario:

    [table]
    CUSTOMER

    [Column]
    CUSTID

    [Value]
    screws:01
    screws:02
    screws:03
    screws:04
    bolts:01
    bolts:02
    bolts:03
    bolts:04


    What I need is a Query that would return all the "CUSTID" values as:

    screws
    bolts

    That is: the value of CUSTID being substringed and applying GROUP BY in order to only return non-duplicates.

    Thanks in advance,

    - L

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Try:

    select substr(custid, 1, instr(custid, ':')-1)
    from customer


    That should always go from

    Digit 1 ------- Digit before colon


    Think the parentheses should be right -try it and let me know.

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Returning partial (substring) of column value

    Select Distinct substr(CUSTID,1,instr(CUSTID,':')-1)
    from customers

  4. #4
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: Returning partial (substring) of column value

    Originally posted by linkey
    I am sure there is an easy way of doing this via SQL though could use some help.

    Here is my scenario:

    [table]
    CUSTOMER

    [Column]
    CUSTID

    [Value]
    screws:01
    screws:02
    screws:03
    screws:04
    bolts:01
    bolts:02
    bolts:03
    bolts:04


    What I need is a Query that would return all the "CUSTID" values as:

    screws
    bolts

    That is: the value of CUSTID being substringed and applying GROUP BY in order to only return non-duplicates.

    Thanks in advance,

    - L
    try
    select distinct substr(CUSTID,1,instr(CUSTID,':') -1 )
    from customer

  5. #5
    Join Date
    Aug 2003
    Posts
    15
    Beautiful!

    Thanks to both of you!

    any performance differences between using DISTINCT apposed to GROUP BY?

    - L

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by linkey
    Beautiful!

    Thanks to both of you!

    any performance differences between using DISTINCT apposed to GROUP BY?

    - L
    distinct supposed to be faster

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    Distinct will only grab one of each value

    So: bolts, screws

    You can only use group by with some sort of function - like count, sum...etc.

  8. #8
    Join Date
    Aug 2003
    Posts
    15
    Thanks for your help!

Posting Permissions

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