How to remove text after a specific character ('.' or ',' or '-')

[#1]

Hello,

Let's say I have a field with reference numbers.

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

I would like to keep only the numbers before the first dot (.)

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

Thank you

[swuehl · Accepted solution]

Try using the Subfield() function:

LOAD *,

Subfield(Test,'.',1) as Extract

INLINE [

Test

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

];

Test	Extract
6444.87	6444
84736.999.1	84736
98374.09	98374
573830.098.9	573830
987464.1	987464
98383	98383
237589	237589

[#3]

Thank you Stefan

[sunny_talwar]

May be try this:

Table:

LOAD *,

  Left(Number, FindOneOf(Number & '.', '.,-')-1) as NewNumber;

LOAD * Inline [

Number

237589

573830.098.9

98374,09

987464-1

84736.999.1

98383

6444-87

];

[vishsaggi]

Try this:

LOAD *,

   IF(Index(Num,'.')>=1, Subfield(LEFT(Num, Index(Num, '.')), '.'), Num) AS Number;

LOAD * INLINE [

Num

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

];