How to check if variable first letter is number? want to have dynamic column name based on whether first letter is number or not

Archive
1

if anyone can help, i would be really appreciate it.
i want to dynamic name the columns when variable doesn;t begin with number like aa, ba then name the column to be aa_beg_bal., but when the variable begin with number, like 1f, then instead of 1f_beg_bal, i want _if_beg_bal

{% set ledger_type =[‘AA’,‘E1’,‘AJ’,‘AU’,‘BA’,‘1F’,‘2F’,‘3F’,‘4F’,‘1B’,‘2B’,‘3B’,‘4B’,‘JA’,‘PA’,‘HA’,‘JY’ ,‘EB’,‘F1’,‘F2’,‘F3’,‘F4’,‘B1’,‘B2’,‘B3’,‘B4’]%}

select
{% for cat_code in ledger_type %}

‘{{cat_code}}’, '{{cat_code}}Beg_Bal’
, {% if {{cat_code}}[0] ==‘1’ %}{% endif %}_{{cat_code}}_Beg_Bal
{% if not loop.last %},{% endif %}
{% endfor %}

2

This is possible, we successfully split a JSON string alphabetically into smaller JSON strings.
Based on the starting letter of the value, we moved it to it’s appropriate destination.
We use BigQuery, a code snippet:
FN = Field Name
FV = Field Value
FS = Field Sequence

{% macro add_to_string_agg(field_name,field_value) %}
    {% set alphabet = ["a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"] %}
    {%- for letter in alphabet -%}
        {% set new_fn =  "FN_" ~  letter|upper %}  
        {% set new_fv =  "FV_" ~  letter|upper %} 
        case 
           when UPPER(SUBSTR({{field_name}},1,1)) = '{{letter|upper}}'
              then FN else "" end as {{new_fn}},
        case 
           when UPPER(SUBSTR({{field_name}},1,1)) = '{{letter|upper}}'
              then STRING_AGG(FV, "" ORDER BY FS,FV) 
           else "" end as {{new_fv}}

        {%- if not loop.last %},{% endif -%}
    {%- endfor -%}
{% endmacro %}

Hope this helps.

3

Thanks so much. this is exactly what i am looking for. will try this

4

Hi Sir, want to ask what would be correct command to get first letter of the string, i tried substr, but it doesn’t work. i tried to use below macro and adjust the columns name based on first letter of string, but doesn’t work. can you help?
{% macro ConvertColumnName(column_name) %}

{% if 9>SUBSTR(column_name,1,1)>0  %}
  {{ return("_" ~ column_name) }}
{% else %}
  {{ return(column_name) }}
{% endif %}

{% endmacro %}