How to count total amount of "commas" before the first digit in SQL?

titutubs 0
Viewed 41

I have a string column1. Now, I want to create a second column which counts the number of commas until the first digit appears.

For example, we have [,,4,] then there are 2 commas before the first digit (4) appears. So outcome would be 2. See table below.

Column1 Desired Output Digit
[,,4,]  2              4   
[2,,,]  0              2

I am using Redshift SQL.

amazon-redshift sql
2 Answers

Use REGEXP_INSTR to find the position of the first digit, then take a substring up to that position and use REGEXP_COUNT to count the commas.

(untested)

REGEXP_COUNT(
  LEFT(Column1, REGEXP_INSTR(Column1, '[[:digit:]]')),
',')
L. Scott Johnson0

Use LTRIM and LEN

SELECT LEN(Column1) - LEN(LTRIM(Column1, ','))

Basically this says take the length of the whole string minus the length of the string stripped of leading commas. The difference is the number of commas stripped.

Jonathan Willcock0
Related