All Forums Connectivity
Venkatesh G 17 posts Joined 05/09
18 May 2009
how to search for char ' and remove from the string "venkat's house"

Hi,Please let me know how to search for the character ' in a string and remove it.data we have in teradata is "venkat's house"i neet to remove ' and convert it into "venkts house".please help me in this regardemail: venkatesh.gubba@gmail.com

Adeel Chaudhry 773 posts Joined 04/08
18 May 2009

Hello,First of all, you should decide whether only 1 instance is to be replaced or any number of instances!You can do that by either developing a UDF, or by using Teradata's built-in string functions. As a hint, concatenation (i.e. string1 || string2) and INDEX function can be very helpful!HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Venkatesh G 17 posts Joined 05/09
18 May 2009

I need to replace the 's with si need to remove ' (all the instances)I am using the following query to find the index:select index(contr_titl_nm,''s')FROM usp_sls_contr_obvw.contrwhich is wrong,please advice

Adeel Chaudhry 773 posts Joined 04/08
18 May 2009

Following is the complete example, HTH:CREATE VOLATILE TABLE Table1(Col1 VARCHAR(20)) ON COMMIT PRESERVE ROWS;INSERT Table1 VALUES ('abc''s');INSERT Table1 VALUES ('abcd''s');INSERT Table1 VALUES ('abcde''s');INSERT Table1 VALUES ('abcdef''s');SELECT Col1 AS "Original Column" , SUBSTRING(Col1, 0, INDEX(Col1, '''')) AS "First Part" , SUBSTRING(Col1, INDEX(Col1, '''') + 1, LENGTH(Col1)) AS "Second Part" , SUBSTRING(Col1, 0, INDEX(Col1, '''')) || SUBSTRING(Col1, INDEX(Col1, '''') + 1, LENGTH(Col1)) AS "Both Parts"FROM Table1;DROP TABLE Table1;Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Venkatesh G 17 posts Joined 05/09
18 May 2009

its working Thank you

You must sign in to leave a comment.