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

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.

CodePudding user response：

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:]]')),
',')

CodePudding user response：

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.