How do I get this done skript

[kkarlste]

Hi

This is a data

This is correct model

letterletterletter-numbernumbernumber

For example BP-100 or AFP-296

This is data   

100BP

100BR

102KAN

106HAO

109HAV

10CBA

10UAG

111ISS

AFO598

AFP296

AFT698

AFU291

AFX404

AGE590

AGM210

AGR941

AGT522

AGT555

AGT561

AGT563

AGT566

AGT923

AGT952

[Miguel_Angel_Baeyens]

Not clear which your requirement is, but perhaps this is close to what you want to achieve:

KeepChar(Field, 'abcdefghijklmnopqrstuvwxzyABCDEFGHIJKLMNOPQRSTUVWXYZ') & '-' & KeepChar(Field, '0123456789') AS CleanField

in the script

[sunny_talwar]

What are you looking to do?

[kkarlste]

Hi

This is good but same column is this 109HAV and this AFP296
This must be first letter and then number
For example

HAV-109

AFP-296

[Miguel_Angel_Baeyens]

That's what I meant by clarifying the requirement:

If(Len(KeepChar(Left(Field, 1), '0123456789')) = 0, // if the first character is not a number

KeepChar(Field, 'abcdefghijklmnopqrstuvwxzyABCDEFGHIJKLMNOPQRSTUVWXYZ') & '-' & KeepChar(Field, '0123456789'))

AS CleanField

[jpar0511]

On my side script of mbaeyens‌ is working as follow:

demo:

load * inline [Field

100BP

100BR

102KAN

106HAO

109HAV

10CBA

10UAG

111ISS

AFO598

AFP296

AFT698

AFU291

AFX404

AGE590

AGM210

AGR941

AGT522

AGT555

AGT561

AGT563

AGT566

AGT923

AGT952]

;

neu:

load *,

KeepChar(Field, 'abcdefghijklmnopqrstuvwxzyABCDEFGHIJKLMNOPQRSTUVWXYZ') & '-' & KeepChar(Field, '0123456789') AS CleanField

resident demo;

drop table demo;

I got everything sorted as you wanted to have it. First letters followed by '-' and then the numbers...

[kkarlste]

Good but not work correct

[Anil_Babu_Samineni]

Seems works to me, Nothing changes on your script

[Miguel_Angel_Baeyens]

I just tested it in case I made any mistake and it is working, as several here have reported. If it doesn't for you either you are not applying the functions in the same order or your data source looks different than your original post, in which case, would be useful to have a sample on your actual data.

[el_aprendiz111]

Hi,