CAST with OREPLACE function

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

CAST with OREPLACE function

 

Hi,

 

When we are using cast with oreplace then the length of the string is changing.

CAST (OREPLACE(<COL>,' ','') AS CHAR(10))

 

Eg - 

Column value - 'A B'

 

When i cast this column as char(10) then the length of the string is 10 but when i use oreplace to remove the embedded space and then use cast as char(10) then the length is changing to 2.

 

Need help as i dont want the length of the string to change and also want to understand why the length is changing even though i am using cast after oreplace

 

 

Thanks.

 


Accepted Solutions
Teradata Employee

Re: CAST with OREPLACE function

Maybe you're using an old version of Teradata ODBC that is substituting the ODBC LENGTH function (which doesn't count trailing spaces) instead of the native database function.

 

Try using CHARACTER_LENGTH instead of LENGTH.

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: CAST with OREPLACE function

How are you determining that result length is 2 rather than 10?

SELECT CAST('A B' AS CHAR(10)) AS x, LENGTH(x), TYPE(x), CAST (OREPLACE(x,' ','') AS CHAR(10)) AS y, LENGTH(y), TYPE(y)

A B 10 CHAR(10) CHARACTER SET UNICODE AB 10 CHAR(10)

 

Enthusiast

Re: CAST with OREPLACE function

Hello fred,

 

I am checking as LENGTH ( CAST ( OREPLACE('A B',' ','') AS CHAR(10) ) ) and getting output as 2 

 

 

Thanks

 

Teradata Employee

Re: CAST with OREPLACE function

Maybe you're using an old version of Teradata ODBC that is substituting the ODBC LENGTH function (which doesn't count trailing spaces) instead of the native database function.

 

Try using CHARACTER_LENGTH instead of LENGTH.

Enthusiast

Re: CAST with OREPLACE function

Thanks Fred.
CHARACTER_LENGTH is giving correct count.