how to search for char ' and remove from the string "venkat's house"

Connectivity
Enthusiast

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 regard
email: venkatesh.gubba@gmail.com

4 REPLIES
Teradata Employee

Re: how to search for char ' and remove from the string "venkat's house"

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
Enthusiast

Re: how to search for char ' and remove from the string "venkat's house"

I need to replace the 's with s

i 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.contr

which is wrong,

please advice
Teradata Employee

Re: how to search for char ' and remove from the string "venkat's house"

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

Enthusiast

Re: how to search for char ' and remove from the string "venkat's house"

its working

Thank you