Skip to content

SQL, extract everything before 5th comma

For example, my column “tags” have


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


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

NVL(SUBSTRING(tags, 1,REGEXP_INSTR(tags,',',1,5) -1),tags)
from myTable



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 contributions licensed under: CC BY-SA
1 People found this is helpful