Sunday, October 4, 2020

PostgreSQL - get position of second delimiter in string

There's many articles with examples of getting the first or last position of a delimiter in a string. Here's an example of getting the position of the second delimiter in a string. 

Environment: 
PostgreSQL 10.12

Get the position of the first delimiter ';' from the public.cherryshoe.description column:
select description, 
position(';' in description) from public.cherryshoe; 

Get the position of the second delimiter ';'.   Add char_length of each split_part - since we want the position, need two char_length's.  Add an additional length of 2 for each split_part function that is used to account for each of the two delimiters:
select description, 
(char_length(split_part(description, ';', 1))
+ char_length(split_part(description, ';', 2)) 
+ 2) from public.cherryshoe;

Get the position of the third delimiter ';'.  Add char_length of each split_part - since we want the position, need three char_length's.  Add an additional length of 3 for each split_part function that is used to account for each of the three delimiters:
select description, 
(char_length(split_part(description, ';', 1)) 
+ char_length(split_part(description, ';', 2)) 
+ char_length(split_part(description, ';', 3)) 
+ 3) from public.cherryshoe;

So on and so forth...

No comments:

Post a Comment

I appreciate your time in leaving a comment!