How to get the position of special char in a string

Database
Enthusiast

How to get the position of special char in a string

Hi all,

How to get the position of special char in a string?

I need to convert the string given below under type A into Type B.

Type A Type B

'Jiby Jose' 'Jiby'
'Jiby: Jose' 'Jiby'
'Jiby?: Jose' 'Jiby'
'Jiby(A) Jose' 'Jiby'

for this I need to find the position of the special char and space in a string.
Please help me in this regard.

If possible please email me to venkatesh.gubba@gmail.com
1 REPLY
Enthusiast

Re: How to get the position of special char in a string

To remove all the Special Characters (Or anything you don't want) from a String - just create the following procedure and Run that for any String as shown below:

/* Procedure Start */

REPLACE PROCEDURE REPLACE_STRING (IN in_VAR VARCHAR(50), OUT out_VAR VARCHAR(50))

BEGIN

DECLARE FLAG INTEGER;

DECLARE STR VARCHAR(50);

SET FLAG = 1;

SET STR = '';

WHILE FLAG <= CHARACTERS(in_VAR)

DO

IF (UPPER(SUBSTR(in_VAR,FLAG,1))) IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') THEN

SET STR = STR || SUBSTR(in_VAR, FLAG, 1);

SET FLAG = FLAG+1;

ELSE

SET FLAG = FLAG+1;

END IF;

END WHILE;

SET out_VAR = STR;

END;

/* Procedure End */

Now Call the Procedure as below:

CALL REPLACE_STRING(' SAN J&O*S!E ', NAME);

You'll get the desired Output as SANJOSE

You'll not get any character other than those characters specified in IN clause in IF statement.

Hope this will solve your query!!!

Regards,

ANIMESH DUTTA