substring problem

reeson · #1 (**permalink**) 07-18-11, 00:01

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?

BrianSteffens · #2 (**permalink**) 07-18-11, 00:26

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.

reeson · #3 (**permalink**) 07-18-11, 00:47

Seems like it works. Thanks so much

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

Cheers BrianSteffens!

BrianSteffens · #4 (**permalink**) 07-18-11, 00:56

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

reeson · #5 (**permalink**) 07-18-11, 01:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech