Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
What are you looking to do?
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
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
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...
Good but not work correct
Seems works to me, Nothing changes on your script
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.
Hi,