CODEWITHSUNDEEP
sqlsql-serverteradata
Ganesh Raja
Ganesh Raja

Reputation: 1

query to merge or combine first name, middle initial and last name in single column, when Ihave full Middle name in few rows?

I am Using Teradata sql assistant.

select (A1.first+', '+A1.last+' '+substr(a1.middle,1,1)) as  iName
from tablename A1

I am getting this error

420: SQL0420N Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018

But when I process the same query in MS sql server studio, I amable to run,

When I use this query

select (A1.first+', '+A1.last+' '+substr(a1.middle,1,1)) as  iName
from tablename A1

I get this error,

171: SQL0171N The data type, length or value of the argument for the parameter in position "3" of routine "SUBSTRING" is incorrect. Parameter name: "". SQLSTATE=42815

Please help me solve this.

Upvotes: 0

Views: 1271

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think the query you want is:

select (A1.first || ', ' || A1.last || ' '+substr(a1.middle, 1, 1)) as  iName
from tablename A1;

Upvotes: 1

Related Questions