Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Unanswered: Accessing substrings

    Hi All,

    I have an attribute kfld which is varchar2 type and has value as shown below: e.g. kfld= "abc= 2bv= 1fed= 30thb= 200"

    my problem is the position of field "fed" is not fixed in the string, So can I get the value '30' (which follows the substring 'fed=' and precedes any alphabet string like tbh, ignore whitespaces) in the output of a single query for a given record.

    Rgrd,
    tk

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select substring(kfld from position('fed=' in kfld)) from ...
    this gets you the substring '30thb= 200'

    however, detecting the first alphabet now becomes a little trickier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Do I win a prize for gratuitous use of string functions?

    This is using SQL Server, but I assume other systems have similar functions.

    Code:
    declare @string as varchar(40)
    set @string='abc= 2bv= 1fed= 30thb= 200'
    
    select ltrim(substring(substring(@string,charindex('fed',@string)+4,len(@string)), 1,patindex('%[a-Z]%',substring(@string,charindex('fed',@string)+4,len(@string)))-1))
    That assumes the number following 'fed' is always followed by a letter. If it's "@" or "$", it won't work. Of course, you need to adapt for your table and column.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no fair!! i was trying to restrict myself to the language of this particular forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I did admit I was cheating. Just trying to get the user going in the right direction. Well, maybe not the RIGHT direction, but a direction none-the-less.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    varchar2 screams Oracle at me.... should we be looking for Oracle solutions?

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The string that is contained looks like it should be normalised in some fashion or another. Perhaps to a table of id, key=>values. Difficult to tell thought without some more meaningful data.

    What string functions are standard in SQL?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    standard SQL string functions are concatenation (accomplished with double pipes ||), UPPER, LOWER, TRIM, TRANSLATE, CHAR_LENGTH, BIT_LENGTH, OCTET_LENGTH, SUBSTRING, and POSITION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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