select/function help required..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select/function help required..

Author

Topic

marco gwiliani
Starting Member

25 Posts

Posted - 2008-04-17 : 14:22:49

I have a select statement running within stored procedure as below. What I need to do is format the results of the select into one string by concatanting the below, and removing any null fields by using ISNULL. I am struggling with syntax. Can anyone advise? Should I be using a function? is so any sample syntax or pointers would be great..thanks marco

SELECT address1, address2, address3, address4, address5
FROM customer

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 14:31:57

SELECT coalesce(address1,'') + coalesce(address2,'') + coalesce(address3,'') + coalesce(address4,'') + coalesce(address5,'')
FROM customer

or maybe
SELECT coalesce(address1+', ','') + coalesce(address2+', ','') + coalesce(address3+', ','') + coalesce(address4+', ','') + coalesce(address5+', ','')
FROM customer

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

marco gwiliani
Starting Member

25 Posts

Posted - 2008-04-17 : 15:01:31

thanks for response. what I need to do is put a space between a column if notnull, and if the column is null ignore (ie no space). So for example, the results of your second query are:

Oxford Road, , Chorlton, , Manchester,

What I need to return is:

Oxford Road Chorlton Manchester

Any ideas? thanks marco

marco gwiliani
Starting Member

25 Posts

Posted - 2008-04-18 : 02:14:08

Managed to get this sorted after 4 hrs! I created a function and called the function as part of my select in the sproc. For each address line which I pass to the function I carry out the below. This way am empty string is ignored and a populated string is concatanated + a space is added. rgds Marco

IF(LTRIM(RTRIM(@address1))) <> ''
set @out = @out + @address1 + ' '

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 07:19:17

quote:
Originally posted by marco gwiliani

thanks for response. what I need to do is put a space between a column if notnull, and if the column is null ignore (ie no space). So for example, the results of your second query are:

Oxford Road, , Chorlton, , Manchester,

What I need to return is:

Oxford Road Chorlton Manchester

Any ideas? thanks marco

Are you sure those are null? If they were it should be
Oxford Road, Chorlton, Manchester,
I suspect they are empty strings rather than null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

marco gwiliani
Starting Member

25 Posts

Posted - 2008-04-18 : 08:08:46

My mistake, they are empty strings, rather than nulls.

rgds Marco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources