June 6, 2013 at 1:09 pm
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
June 6, 2013 at 1:16 pm
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
June 6, 2013 at 1:20 pm
Just another option
select CASE WHEN col1 + col2 IS NULL
THEN COALESCE( col1, col2, '')
ELSE col1 + '/' + col2 END as secondtry
from Table_1
June 6, 2013 at 1:21 pm
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
June 6, 2013 at 1:21 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 1:34 pm
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
June 6, 2013 at 1:42 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 9:53 pm
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
Change is inevitable... Change for the better is not.
June 6, 2013 at 10:39 pm
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.
June 6, 2013 at 10:51 pm
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
Change is inevitable... Change for the better is not.
June 6, 2013 at 11:03 pm
P.S. The 3 operand COALESCE will work fine if you want to return a NULL if both names are NULL. If, however, you want it to return an empty string if both names are NULL, just add '' as the 4th operand.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2013 at 7:36 am
Wow that is super slick Jeff. Don't why none of the rest of us thought about that.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 7:49 am
I just get lucky with being lazy, sometimes. Thanks for the feedback, Sean.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2013 at 8:02 am
Jeff Moden (6/7/2013)
I just get lucky with being lazy, sometimes. Thanks for the feedback, Sean.
Nobody is consistently that lucky, that indicates a fair amount of skill laying under all that luck.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 8:15 am
Excellent solutions.
This is just a small part of the massive procedure I am currently modifying but performance of this piece has improved.
Thanks all.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy