how to identify lower/upper case/special characters with in a field

[Anonymous]

ex

House- correct format

HOUSE-Upper case

house-Lower case

#$house$- Special character

need help in scripting on

how to check a field whether

it is correctformat/upper case/

lower case/special character

[sdmech81]

U mean u want to convert ur column in to house upon checking fr tht??

Send data..

Sachin

[sunny_talwar]

May be this:

If(Len(Trim(KeepChar(FieldName, '!@#$%^&*()'))) > 0, 'Special Character',

     If(Field = Capitalized(Field), 'Correct Format',

          If(Field = Upper(Field), 'Upper Case',

               If(Field = Lower(Field), 'Lower Case')))) as Flag

[Chanty4u]

Hi,

Am not sure but this will give you

a:

LOAD * INLINE [

    Data

    House- correct format

    HOUSE-Upper case

    house-Lower case

    #$house$- Special character

    need help in scripting on

    how to check a field whether

    it is correctformat/upper case/

    lower case/special character

];

Result:

LOAD

KeepChar(Data,'abcdefghijklmnopqrstuvwxyz') as lower,

KeepChar(Data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') AS Uupper,

KeepChar(Data,'!@#$%^&*()') as case

Resident a;

[awhitfield]

Hi raga,

1. to set the case, use Capitalize, e.g.

Capitalize(data) as data

2. to remove the unwanted characters, use purge char / keep

See the following: PurgeChar and KeepChar Functions

Andy

[Anonymous]

thanks all for your replies.

the requirment i have is i need to capture

rows only which are in incorrect format.

no name

1 Apple

2 CAT

3 $Dog

4 hen

output should load only 2,3,4 rows

as all of these are not in standard format.

i also need the error message for those

2,3,4 rows as

no name remarks

2 CAT complete nam in upper cas

3 $Dog has special characters

4 hen complete name in lower case

plz help.

thanks

[sunny_talwar]

Try a where statement like this:

Table:

LOAD * Inline [

Field

Apple

CAT

$Dog

hen

]

WHERE Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0 or Field = Upper(Field) or Field = Lower(Field);

[sunny_talwar · Accepted solution]

Added error message field

Table:

LOAD *,

  If(Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0, 'Special Character',

    If(Field = Capitalize(Field), 'Correct Format',

          If(Field = Upper(Field), 'Upper Case',

              If(Field = Lower(Field), 'Lower Case')))) as [Error Message];

LOAD * Inline [

Field

Apple

CAT

$Dog

hen

]

WHERE Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0 or Field = Upper(Field) or Field = Lower(Field);

[Anonymous]

Thank you

[Anonymous]

Hi Sunny,

How about for this case. 5,6 rows

no name

1 Apple

2 CAT

3 $Dog

4 hen

5 GOAt

6 bOaT

no name remarks

2 CAT complete nam in upper cas

3 $Dog has special characters

4 hen complete name in lower case

5 GOAt Irregular case format

6 bOaT Irregular case format