CODEWITHSUNDEEP
sqlsql-serverdb2
Able
Able

Reputation: 3912

Convert DB2 TRANSLATE function into SQL Server

I would like to convert the below DB2 query into SQL Server

SELECT TRANSLATE(' 11', '*', ' 0123456789') FROM SYSIBM.SYSDUMMY1

the above query result is * since the first argument matches with the values in the third argument.

What is the equivalent functionality available in the SQL server? SQL Server has a TRANSLATE function, but the functionality is different.

Upvotes: 0

Views: 1530

Answers (1)

Martin Smith
Martin Smith

Reputation: 453563

In DB2 the result of TRANSLATE(' 11', '*', ' 0123456789') is * (DB 2 Fiddle)

In SQL Server you need to use (SQL Server Fiddle)

SELECT TRANSLATE(' 11', ' 0123456789', '*          ')

The DB2 version will by default implicitly pad the to-string out with spaces to match the length of the from_string.

If the length of to-string is less than the length of from-string, to-string is padded to the length of from-string with the pad or a blank

In SQL Server you need to do any such manipulation to the to-string before calling the function or you will get the error

The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

SQL Server has no padding logic built into the TRANSLATE function itself. Also the order of the 2nd and 3rd arguments is reversed between the two systems.

Upvotes: 3

Related Questions