问题

I have a code that counts spaces before a string - see sample table called TEST

在这种情况下，我想要计算在"EB"前面的空格数量，应该是2，但我的查询返回1，因为它将"FEB"视为"EB"。如何使查询只计算在"EB"之前的空格？

Thanks!

select id
, REGEXP_COUNT(  
                SPLIT( 
                   TRIM(
                     REGEXP_REPLACE(value, '[:digit:]', ' ')
                   ), 'EB'
                 )[0] , ' '
             ) count_of_spaces
from  TEST

请注意，我已经移除了HTML编码并翻译了代码部分。

select id
, REGEXP_COUNT(  
            SPLIT( 
               TRIM(
                 REGEXP_REPLACE(value, '[^[:digit:]]', ' ')
               ), 'EB'
             )[0] , ' '
         ) count_of_spaces
from  TEST

答案1

得分: 1

如果您按空格分割，然后请求'EB'的[ARRAY_POSITION][1]，您将找到第一个精确匹配位置：

select column1    
    ,SPLIT(column1, ' ') as s
    ,ARRAY_POSITION('EB'::variant, s) as p 
from values
 ('EB'), 
 ('FEB EB'), 
 ('AB FEB EB'), 
 ('AB FEB EBX EB'), 
 ('AB FEB EB EBX'), 
 ('AB FEB FEB EB AB FEB FEB EB')
;

[1]: https://docs.snowflake.com/en/sql-reference/functions/array_position.html

<details>
<summary>英文:</summary>

If you split by space, and then ask for the [ARRAY_POISTION][1] of &#39;EB&#39; you will find the exact first match location:

select column1
,SPLIT(column1, ' ') as s
,ARRAY_POSITION('EB'::variant, s) as p
from values
('EB'),
('FEB EB'),
('AB FEB EB'),
('AB FEB EBX EB'),
('AB FEB EB EBX'),
('AB FEB FEB EB AB FEB FEB EB')
;

COLUMN1	|S	|P
--|--|--
EB	|[   "EB" ]	|0
FEB EB	|[   "FEB",   "EB" ]	|1
AB FEB EB	|[   "AB",   "FEB",   "EB" ]	|2
AB FEB EBX EB	|[   "AB",   "FEB",   "EBX",   "EB" ]	|3
AB FEB EB EBX	|[   "AB",   "FEB",   "EB",   "EBX" ]	|2
AB FEB FEB EB AB FEB FEB EB	|[   "AB",   "FEB",   "FEB",   "EB",   "AB",   "FEB",   "FEB",   "EB" ]	|3


  [1]: https://docs.snowflake.com/en/sql-reference/functions/array_position.html

</details>