Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    24

    Unanswered: substring problem

    hi all,

    i have a string = '26453|20110411'.

    how do i select first 6 character AFTER the pipe (|)?
    So the output would be 201104.

    Length of character before | might varies.

    Any ideas?
    Last edited by reeson; 07-18-11 at 01:03. Reason: more info

  2. #2
    Join Date
    Jul 2011
    Posts
    14
    There's no function to do this that I know of, but it's possible - albeit convoluted - with something like the following:

    Code:
    select
    
      replace(
        substring(
          substring_index(val, '|', 2),
          length(substring_index(val, '|', 1)) + 1
        ),
        '|', ''
      )
    
    from sometable;
    Where val is a char/varchar type in sometable.

    I confirmed this worked with 26453|20110411 and 264531|20110411.

  3. #3
    Join Date
    May 2011
    Posts
    24
    Seems like it works. Thanks so much

    Will look up what substring_index do, never use it before.

    Cheers BrianSteffens!

  4. #4
    Join Date
    Jul 2011
    Posts
    14
    You may have already figured this out, but for your uses it can actually be a lot simpler. The code I posted can be used to get any value in a list of delimiter-separated values.. but since you only have the 2 values separated by one delimiter, the following should work as well:

    substring_index(val, '|', -1)

    Much shorter

  5. #5
    Join Date
    May 2011
    Posts
    24
    After going through the manual, think I got it. Pretty neat function.

    I almost going to write my own function for this issue

    Thanks again mate

Posting Permissions

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