SQL, extract everything before 5th comma

user_14312273 0
Viewed 45

For example, my column "tags" have

"movie/spiderman,genre/action,movie:marvel", 
"movie/kingsman,genre/action",
"movie/spiderman,genre/action,movie:marvel,movie:dfjkl,movie:fskj,movie:aa,movie:mdkk"

I'm trying to return everything before 5th comma. below is the result example

"movie/spiderman,genre/action,movie:marvel", 
"movie/kingsman,genre/action",
"movie/spiderman,genre/action,movie:marvel,movie:dfjkl,movie:fskj"

I've tried below code but it's not working.

select
NVL(SUBSTRING(tags, 1,REGEXP_INSTR(tags,',',1,5) -1),tags)
from myTable
amazon-redshift amazon-web-services sql regex
1 Answers

You can use

REGEXP_REPLACE(tags, '^(([^,]*,){4}[^,]*).*', '\\1')

See the regex demo.

The REGEXP_REPLACE will find the occurrence of the following pattern:

  • ^ - start of string
  • (([^,]*,){4}[^,]*) - Group 1 (\1 refers to this part of the match): four sequences of any zero or more chars other than a comma and a comma, and then zero or more chars other than a comma
  • .* - the rest of the string.

The \1 replacement restores Group 1 value in the resulting string.

user_38329700
Related