Querying records where string length is less than N

Luck Kanthatham-2308338

I have a table with US Zip codes where some zips with leading zeros were not imported properly (the data type was originally integer) when importing from Airtable.

I would like to prefix these zip codes with a "0".  However, upon looking at Querying All Record function, I can't seem to add a Strlen filter in the WHERE clause on the field.

In Xano, I wanted to do this:
1.  Query all records where the length of the postal_code is less than 5
2. for each record found, prepend a "0"


In SQL Server, I can do something like this:

update US_Zip
set postal_code = '0' + postal_code
where len(postal_code) < 5

Michael Udinski

Hey  - the easiest thing to do here would be to make that field a text type then you can store a preceding zero. Otherwise it will require transformation via a for each loop in the function stack everytime

Luck Kanthatham-2308338

Thank you, Michael.

Understood.  However, the tables were already imported that way.  Airtable made the zip code field integer.

What I was looking for was a filter that would allow me to apply the where clause on the length of the data in a certain field.  Something like:

WHERE db.US_Zip.postal_code + filter strlen() < 5

on the Query All Records function.

Perhaps this could be a feature request?

Stefan Pointecker

Would need the length filter as well currently.