IF statement with a "begins with" clause

[dawgfather]

I am trying to determine how to best create an IF statement and use a condition that "if the field starts/begins with..." then do this, otherwise do that.

I do not want to use a contains type command because some of the characters might appear later in the string, which is OK.

How do I craft an IF statement that would look at the sample fields below and "if the field starts with a '1', then output 'number', otherwise output 'text'. I will likely expand this as a nested if statement in case the field starts with a 2, or a 3, etc.

Sample

10.10.10.10

Test10.10.10.10

100.10.10.10

1.10.10.10

Sample100.1.1.1

Results in

Number

Text

Number

Number

Text

[cauecandeloro]

Hi,

You can use 'mid'. Ex:

If(

mid(Sample,1,1) = 1, 'Number','Text')

Caue.

[sunny_talwar · Accepted solution]

May be this:

If(Left(Sample, 1) = 1, 'Number', 'Text') as Result

or more generally for any number, you can try this:

If(IsNum(Left(Sample, 1)), 'Number', 'Text') as Result

Script:

Table:

LOAD *,

  If(IsNum(Left(Sample, 1)), 'Number', 'Text') as Result;

LOAD * Inline [

Sample

10.10.10.10

Test10.10.10.10

100.10.10.10

1.10.10.10

Sample100.1.1.1

];

[Miguel_Angel_Baeyens]

The easiest way to get what you want is using WildMatch() as in

If(WildMatch(Field, '1*') > 0, 'Number', 'Text') AS IsTextFlag

Miguel

[swuehl]

Or maybe a more general approach to test all contained characters:

If( Len( Keepchar( Field, '1234567890.')) = Len(Field), 'Number','Text'))