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

[sasikanth]

HI,

If this is the only case left to validate then you can give that in Else part

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','Irregular Format')))) as [Error Message]

[Anonymous]

Ok thank you. Modified script as below. and output is as required.

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','Irregular Format')))) as [Error Message];

LOAD * Inline [

Field

Apple

CAT

$Dog

hen

gOaT

BOAt

]

WHERE Field <>  Capitalize(Field);

Output:

[sasikanth]

Great

[Anonymous]

One more doubt is , is this cant be achieved with ASCII code. Like

assuming ASCII code as below

no	Char	no	Char	Dec	Char	Dec	Char	Dec	Char	Dec	Char
32	SPACE	91	[	48	0	65	A	97	a	82	R
33	!	92	\	49	1	66	B	98	b	83	S
34	"	93	]	50	2	67	C	99	c	84	T
35	#	94	^	51	3	68	D	100	d	85	U
36	$	95	_	52	4	69	E	101	e	86	V
37	%	96	`	53	5	70	F	102	f	87	W
38	&	123	{	54	6	71	G	103	g	88	X
39	'	124	|	55	7	72	H	104	h	89	Y
40	(	125	}	56	8	73	I	105	i	90	Z
41	)	126	~	57	9	74	J	106	j	114	r
42	*	47	/			75	K	107	k	115	s
43	+	58	:			76	L	108	l	116	t
44	,	59	;			77	M	109	m	117	u
45	-	60	<			78	N	110	n	118	v
46	.	61	=			79	O	111	o	119	w
63	?	62	>			80	P	112	p	120	x
64	@					81	Q	113	q	121	y
										122	z

Is there a way where we can mention

1. if characters b/w these range it is Special characters

2. For Irregular format , where we can check each letter and specify whether it is in standarad format or not.

ex ; ElePhaNt

here can we use any loops to check each letter within a field

like E is first word of the filed, next l is second,e is third and so on. so that all words are checked for format.

[sunny_talwar]

Great improvisation. Thanks for sharing

[sunny_talwar]

1) You should be able to use MapSubString() with a Mapping load to do this

2) I am not sure I understand your description here

[MarcoWedel]

Hi,

maybe one solution regarding the loops to check each letter could be:

tabWords:  
LOAD RecNo() as ID ,* 
INLINE [
    Word
    Apple
    2Apples
    CAT
    Cat3
    $Dog
    hen
    gOaT
    BOAt
    House
    5Houses
    HOUSE
    house
    #$house$
    ElePhaNt
    ϘλικVιεω
];

tabWordChar:
LOAD ID,
     Mid(Word,IterNo(),1) as Char,
     IterNo() as Position
Resident tabWords
While IterNo()<=Len(Word);

tabChar:
Left Keep (tabWordChar)
LOAD *,
     Pick(Match(1,[Upper Case Letter]*1,[Lower Case Letter]*1,Number*1,[Special Character]*1),'A','a',Text('0'),'$') as CharSymbol;
LOAD *,
     If(Letter and Char=Upper(Char),Dual('Yes',1),Dual('No',0)) as [Upper Case Letter],
     If(Letter and Char=Lower(Char),Dual('Yes',1),Dual('No',0)) as [Lower Case Letter],
     If(not (Letter or Number),Dual('Yes',1),Dual('No',0)) as [Special Character];
LOAD *,
     If(IsNum(Char),Dual('Yes',1),Dual('No',0)) as Number,
     If(Upper(Char)<>Lower(Char),Dual('Yes',1),Dual('No',0)) as Letter;
LOAD RecNo() as Ord,
     Chr(RecNo()) as Char      
AutoGenerate 65535;

mapChar:
Mapping LOAD Char, CharSymbol Resident tabChar;

Left Join (tabWords)
LOAD Distinct
     Word,
     MapSubString('mapChar',Word) as WordFormat
Resident tabWords;

tabCharType:
CrossTable (CharType, CharTypeYesNo)
LOAD Distinct
     Char,
     Number,
     Letter,
     [Upper Case Letter],
     [Lower Case Letter],
     [Special Character]
Resident tabChar;
Right Join LOAD 1 as CharTypeYesNo AutoGenerate 1;
DROP Field CharTypeYesNo;

hope this helps

regards

Marco

[Anonymous]

this is matching with my requirement sir, thanks a ton.

[MarcoWedel]

You're welcome.

Glad it helped

Regards

Marco