Need to create macro but facing issue while writing jinja logic

Help
1

HI,

I need to create a macro in dbt core where it will take a string input (column) value and tried to convert it to required case:
(we have a equivalent function in sql server which will iterate through each characters of a given column value string, check if it is first character or character after any spaces or special char and it will make it to upper else lower)

Every first alpha character of a string within each word should be in Caps
If any number or space or any special characters in between words, it should make next character to that in Caps
for eg.
Input =‘hellO wOrLD 1d3’
output=‘Helllo World 1D3’

I tried writing below Jinja logic to check if it is correctly converting it to upper or lower, howeever it is not going to lower block

{% set text = ‘1hd2f’ %}
{% set ret = ‘’ %}
{% set varreset = 1 %}

{% for c in text -%}
{% set ret = ret ~ (c.upper() if varreset == 1 else c.lower()) %}
{{ret}}
{% set varreset = 0 if c.isalpha() else 1 %}
{{varreset}}
{%- endfor %}

Output i am getting is:
1
1
H
0
D
0
2
1
F
0

But it should return ‘1Hd2F’

Can someone please help on this to write correct macro/jinja logic

2

Hi @sgavhane, even if you make it work with your jinja variable ‘text’, you will not make it work with the column values.

Jinja just renders SQL code, it does not have access to your column’s values So Jinja does not know the column values, it will not know the text inside the column and it will not manipulate it.

You can try finding some SQL functions from your DW that do what you want or using python models