Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Combine columns into One with Delimiter Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 1:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:20 AM
Points: 49, Visits: 214
I have the following:


Create table Table_1 
(
[col1] varchar(10) null,
[col2] varchar(10) null,
)

Insert Table_1(col1, col2)
values ('Bill','Smith'),('Bill',null),(null,'Smith')

select rtrim(isnull(col1+'/',''))+rtrim(isnull(col2+'','')) as firsttry from Table_1


This returns:

Bill/Smith
Bill/
Smith

I am trying to remove the trailing '/' if col2 is null from col1.

Was playing around with Reverse but couldn't get it to work...
Any ideas would be appreciated.

Thanks
Post #1460858
Posted Thursday, June 6, 2013 1:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 3,664, Visits: 7,995
It's not an elegant solution but it works
select isnull(col1,'') + ISNULL( RIGHT( col1 + col2 + '/', 1), '') +rtrim(isnull(col2,'')) as firsttry from Table_1




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460861
Posted Thursday, June 6, 2013 1:20 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 3,664, Visits: 7,995
Just another option

select CASE WHEN col1 + col2 IS NULL 
THEN COALESCE( col1, col2, '')
ELSE col1 + '/' + col2 END as secondtry
from Table_1




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460863
Posted Thursday, June 6, 2013 1:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 12,918, Visits: 32,088
my verison is kinda of wordy but works with a case statement:
SELECT
CASE
WHEN RTRIM(ISNULL(col1, '')) <> ''
AND RTRIM(ISNULL(col2, '')) <> ''
THEN RTRIM(ISNULL(col1, ''))
+ '/'
+ RTRIM(ISNULL(col2, ''))
ELSE RTRIM(ISNULL(col1, ''))
+ ''
+ RTRIM(ISNULL(col2, ''))
END AS firsttry
FROM Table_1



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1460864
Posted Thursday, June 6, 2013 1:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 13,481, Visits: 12,337
Here is another:

select stuff(isnull(col1 + '/', '') + isnull(col2 + '/', ''), LEN(isnull(col1 + '/', '') + isnull(col2 + '/', '')), 1, '')
from Table_1



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460865
Posted Thursday, June 6, 2013 1:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 3,664, Visits: 7,995
Sean Lange (6/6/2013)
Here is another:

select stuff(isnull(col1 + '/', '') + isnull(col2 + '/', ''), LEN(isnull(col1 + '/', '') + isnull(col2 + '/', '')), 1, '')
from Table_1



Sean, you gave me an idea.
select stuff(isnull('/' + col1, '') + isnull('/' + col2, ''), 1, 1, '')
from Table_1




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460867
Posted Thursday, June 6, 2013 1:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 13,481, Visits: 12,337
Luis Cazares (6/6/2013)
Sean Lange (6/6/2013)
Here is another:

select stuff(isnull(col1 + '/', '') + isnull(col2 + '/', ''), LEN(isnull(col1 + '/', '') + isnull(col2 + '/', '')), 1, '')
from Table_1



Sean, you gave me an idea.
select stuff(isnull('/' + col1, '') + isnull('/' + col2, ''), 1, 1, '')
from Table_1



I like it!!! Turned it around and avoided the whole len check. Great approach.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1460870
Posted Thursday, June 6, 2013 9:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 37,104, Visits: 31,658
Luis Cazares (6/6/2013)
Sean Lange (6/6/2013)
Here is another:

select stuff(isnull(col1 + '/', '') + isnull(col2 + '/', ''), LEN(isnull(col1 + '/', '') + isnull(col2 + '/', '')), 1, '')
from Table_1



Sean, you gave me an idea.
select stuff(isnull('/' + col1, '') + isnull('/' + col2, ''), 1, 1, '')
from Table_1



No wonder my post count is so low lately. Between you, Sean, and some of the other heavy hitters, I don't have much to do anymore. Well done!

If you're guaranteed to have only 2 columns, a slight variation on the COALESCE approach would work. Haven't checked it for performance, but it might be a little faster because it only does 2 concatenations instead of 3.

 SELECT COALESCE(Col1+'/'+Col2,Col1,Col2)




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1460941
Posted Thursday, June 6, 2013 10:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 3,664, Visits: 7,995
Jeff Moden (6/6/2013)
Luis Cazares (6/6/2013)
Sean Lange (6/6/2013)
Here is another:

select stuff(isnull(col1 + '/', '') + isnull(col2 + '/', ''), LEN(isnull(col1 + '/', '') + isnull(col2 + '/', '')), 1, '')
from Table_1



Sean, you gave me an idea.
select stuff(isnull('/' + col1, '') + isnull('/' + col2, ''), 1, 1, '')
from Table_1



No wonder my post count is so low lately. Between you, Sean, and some of the other heavy hitters, I don't have much to do anymore. Well done!

If you're guaranteed to have only 2 columns, a slight variation on the COALESCE approach would work. Haven't checked it for performance, but it might be a little faster because it only does 2 concatenations instead of 3.

 SELECT COALESCE(Col1+'/'+Col2,Col1,Col2)




How come neither of us got that simple and effective solution? I need to go back to practice the KISS mantra.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460948
Posted Thursday, June 6, 2013 10:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 37,104, Visits: 31,658
Luis Cazares (6/6/2013)
How come neither of us got that simple and effective solution? I need to go back to practice the KISS mantra.


BWAAA-HAAA!!! I have null idea of what you're talking about.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1460951
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse