HELP CENTER

HELP CENTER

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Beast Mode, ETL/Dataflow
- :
- How to find first non-zero number in TEXT data typ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

08-21-2020
05:51

08-21-2020
05:51

Hi,

I have a business requirement where i need to find first non-zero number **in a TEXT ** column which has leading zeros. The position of non-zero number is not fixed in text column.

Can Anyone help me to find logic for the same in Domo ETL?

Below is the example where i need to find whether the __text begins with number 2 by ignoring leading zero's .__

__( Please Note - The data type is Text for below data )__

1) 002200

2) 000050

3) 500000

4) 020020

Please assist me with the same.

All your help is appreciated.

Accepted Solutions

Highlighted

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

08-21-2020
09:04

08-21-2020
09:04

For clarificaiton, after you convert the string to a number you'll need to convert it back to a string and then use the string operations tile to do a left substring to get the 1st character. This will get you the first number to check against.

There are other options utilizing a regex which are a bit more complex but this simple one will pull the first digit it sees that's between 1 and 9 (excluding 0s). Again, this has the assumption you only have a single number in your text.

RegEx (Regular Expression):

`'^[^1-9]*([1-9]).*$'`

You can use this in a Replace Text tile and skip all the intermediary steps, going straight from your dataset instead.

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

Highlighted

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

a month ago

a month ago

just convert your column to Numeric then back to text and take a LEFT 1.

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

All Replies

Highlighted
##

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

08-21-2020
06:38

08-21-2020
06:38

Hi @user11651

Regular Expressions were made for scenarios just like this one. I'd recommend you do a Google search for regular expressions to learn more about them in depth if you're not familiar with them. Another good resource is https://www.regular-expressions.info/

As for your specific case Domo does allow regular expressions to be used when doing a text replace. Before we go down regular expressions though will that be the ONLY number in your text field or is it possible to have multiple numbers in each record?

If it's only ever going to be a single number the easier route would be to use the Text Formatting tile and set the "How should numbers be handled?" to "Only show numbers". That will strip out any non-numeric character. If you're wanting to then ignore any leading 0s you can use a Set Column Type tile to convert that field to an integer. Again this only works if you can be certain that the number you're looking for will be the ONLY number in the text.

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

Highlighted

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

08-21-2020
09:04

08-21-2020
09:04

For clarificaiton, after you convert the string to a number you'll need to convert it back to a string and then use the string operations tile to do a left substring to get the 1st character. This will get you the first number to check against.

There are other options utilizing a regex which are a bit more complex but this simple one will pull the first digit it sees that's between 1 and 9 (excluding 0s). Again, this has the assumption you only have a single number in your text.

RegEx (Regular Expression):

`'^[^1-9]*([1-9]).*$'`

You can use this in a Replace Text tile and skip all the intermediary steps, going straight from your dataset instead.

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

Highlighted

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

a month ago

a month ago

just convert your column to Numeric then back to text and take a LEFT 1.

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

2 weeks ago

2 weeks ago

Thanks @GrantSmith @jaeW_at_Onyx for your quick assistance.

I have used another logic.

Using "Text Formatting" component I have extracted Number Only using (Show Only Numbers). This helped me to remove any non-numeric character. Then I type casted it from string to integer.

After this again i type casted integer to string (This helped me to directly remove leading zeros) by giving first non-zero number

Announcements

Click here to submit your story.