Removing '.' (dot) from input string

Tools & Utilities
Enthusiast

Removing '.' (dot) from input string

Hi ,
I have input field with datatype CHAR(6), type of input are (12.34; 334.56; 765; 97.42).
My requirement is removing '.' (DOT) from input string. so the output should become (1234; 33456; 765; 9742).

If anybody can help in this case plz let me know.

thkx,
Yuva.
4 REPLIES
Enthusiast

Re: Removing '.' (dot) from input string

--create the table

CREATE SET TABLE test ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
col2 CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( col2 );

--Insert data into the column col2
12.34
334.56
97.42
765

--Use this SQL
SELECT
col2 AS test1
,
CASE
WHEN SUBSTR(test1, 1,1) = '.' THEN ''
ELSE SUBSTR(test1, 1,1)
END
||
CASE
WHEN SUBSTR(test1, 2,1) = '.' THEN ''
ELSE SUBSTR(test1, 2,1)
END ||
CASE
WHEN SUBSTR(test1, 3,1) = '.' THEN ''
ELSE SUBSTR(test1, 3,1)
END ||
CASE
WHEN SUBSTR(test1, 4,1) = '.' THEN ''
ELSE SUBSTR(test1, 4,1)
END ||
CASE
WHEN SUBSTR(test1, 5,1) = '.' THEN ''
ELSE SUBSTR(test1, 5,1)
END ||
CASE
WHEN SUBSTR(test1, 6,1) = '.' THEN ''
ELSE SUBSTR(test1, 6,1)
END ||
CASE
WHEN SUBSTR(test1, 7,1) = '.' THEN ''
ELSE SUBSTR(test1, 7,1)
END AS
new_text
from test

--For a good example see this URL http://www.teradataforum.com/teradata/20060314_164454.htm and thanks to Frank

Enthusiast

Re: Removing '.' (dot) from input string

What if we have to deal with Varchar?

Can anybody post a udf that solves this problem?

Re: Removing '.' (dot) from input string

CREATE SET TABLE jg_test_x ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
col2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( col2 );

--Insert data into the column col2

INSERT INTO jg_test_x
values ('97.42') ;

INSERT INTO jg_test_x
values ('334.56') ;

INSERT INTO jg_test_x
values ('765') ;

INSERT INTO jg_test_x
values ('12.34') ;

INSERT INTO jg_test_x
values (NULL) ;

INSERT INTO jg_test_x
values ('') ;

-- remove any dot in the string
-- assumes only one dot
SELECT col2
, CASE
WHEN POSITION ('.' IN col2) > 0
THEN SUBSTRING(col2 FROM 1 FOR (POSITION ('.' IN col2)) -1) || SUBSTRING(col2 FROM (POSITION ('.' IN col2)) +1)
ELSE col2
END
FROM jg_test_x
;

col2
334.56 33456
765 765
12.34 1234
? ?

97.42 9742
Enthusiast

Re: Removing '.' (dot) from input string

Try this

SEL
POSITION('.' IN TRIM(STR)) AS POS
,SUBSTRING(TRIM(STR) FROM 1 FOR (POS-1)) || SUBSTRING(TRIM(STR) FROM (POS + 1) FOR CHARACTER_LENGTH(TRIM(STR)))
FROM ;

Vinay