Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

In-DB Filter tool - find all records with a 6 in the 4th place, e.g. ###6######

oracleoftemple
9 - Comet

I have a 10 digit field (actually it's stored as a string, but all the characters are numbers), and I'm trying to use the In-DB Filter tool to find all the records that have the number 6 in the 4th character's place in that field.  I can do this with a normal filter using REGEX Match.  This is what that filter looks like:  REGEX_Match([HASH_NUM], "\d{3}6\d{6}").  I just can't figure out how to do the same thing with the In-DB Filter tool.  Can anybody help me with this?

7 REPLIES 7
Thableaus
17 - Castor
17 - Castor

Hi @oracleoftemple 

 

What database are you using?

 

In-DB formula and filter depend on the DB syntax.

 

Cheers,

oracleoftemple
9 - Comet

I believe it's called a Generic ODBC.  It has a SQL Editor on the Choose Table or Specify Query screen.  Does that help?

Thableaus
17 - Castor
17 - Castor

@oracleoftemple 

 

No, I mean, which database vendor?

 

Oracle, SQL Server, MySQL, Amazon Redshift...

oracleoftemple
9 - Comet

Ok I just checked - it's SQL Server.

Thableaus
17 - Castor
17 - Castor

@oracleoftemple 

 

Try using the filter In-DB tool like this

RIGHT(LEFT(Field, 4), 1) = "6"

 

Would that work?

Thableaus
17 - Castor
17 - Castor

@oracleoftemple 

 

If it doesn't work please take a screenshot of the configuration of your In-DB filter tool.

 

Cheers,

oracleoftemple
9 - Comet

@Thableaus wrote:

@oracleoftemple 

 

No, I mean, which database vendor?

 

Oracle, SQL Server, MySQL, Amazon Redshift...


Got it!  SUBSTRING("INVC_NUM", 4, 1) = 6

Labels