Any SQL gurus?

[lozina]

I would like a universal sql statement (universal meaning it will work on all databases) to pull a full name from a table where the first name, last name and middle initial are all separated. The tough part is incorporating the middle initial with proper spacing...

for example:

first_name = john
last_name = smith
middle_initial = x

I could do: select first_name || ' ' || middle_initial || ' ' || last_name which will work great if there is a middle initial, but if that field is null then it prints the name with two spaces between the first name and last name. Is there a way ?

 

[Ameesh]

Oracle doesnt believe in universal.

 

[Ameesh]

but to be somewhat helpful you could write a stored procedure to do this, but you shouldnt be trying to format your data too much in your SQL statement, have some auxillary code to do this so your routines to fetch the data and the routine to transform the data are not so coupled.

 

[lozina]

Originally posted by: Ameesh
but to be somewhat helpful you could write a stored procedure to do this, but you shouldnt be trying to format your data too much in your SQL statement, have some auxillary code to do this so your routines to fetch the data and the routine to transform the data are not so coupled.

you know, I think I will follow that advice. I was just being lazy, modifying the sql woulda been an easy way out 🙂

 

[Ameesh]

Originally posted by: lozina

Originally posted by: Ameesh
but to be somewhat helpful you could write a stored procedure to do this, but you shouldnt be trying to format your data too much in your SQL statement, have some auxillary code to do this so your routines to fetch the data and the routine to transform the data are not so coupled.

you know, I think I will follow that advice. I was just being lazy, modifying the sql woulda been an easy way out 🙂

yeah in general its almost always better to decouple this stuff, thats why we have XSLT for XML and other such pairs of technologies.

 

[ggavinmoss]

This appears to work in Sybase:

SELECT fname + (SELECT CASE WHEN #table.mname = NULL THEN ' ' ELSE ' ' + #table.mname + ' ' END) + lname from #table

-geoff

 

[Ameesh]

Originally posted by: ggavinmoss
This appears to work in Sybase:

SELECT fname + (SELECT CASE WHEN #table.mname = NULL THEN ' ' ELSE ' ' + #table.mname + ' ' END) + lname from #table

-geoff

dont get him started in bad habits

 

[Jeraden]

you could be cheap and do

select replace(first_name || ' ' || middle_name || ' ' || last_name,'xx','x')
from table

(this board doesn't display double spaces properly... xx = 2 spaces, x = 1 space)


If you are using Oracle there are several ways (I don't know what functions are oracle specific and which are universal). This would be how I'd do it in oracle:
select first_name || ' ' || nvl2(middle_name, middle_name || ' ',null) || last_name
from table