Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL - concatenate fields that are not empty into a single string

Posted on 2012-03-17
13
Medium Priority
?
457 Views
Last Modified: 2012-03-20
I have a table tbl1

Name | account code | Type  | detail    | description
---------------------------------------------------------------------------
P1       | 12341              | Open | detail1  | description1
P2       | 12342              | Close | detail2  |
P3       | 12343              | Close |              | description2            
P4       | 12344              | Close |

I would like to do a Select that will selectively concatenate the information into a description if the information is available.

This is the logic I try to implement
if detail is not null and description is not null
Select Name + ' ' +  accountcode + ' ' +  Type + ' ' + detail + ' ' + description from tbl1
else if detail is  null and description is not null
Select Name + ' ' +  accountcode + ' ' +  Type + ' ' + description from tbl1
else if detail is  not null and description is null
Select Name + ' ' +  accountcode + ' ' +  Type + ' ' + detail from tbl1
else if detail is  null and description is null
Select Name + ' ' +  accountcode + ' ' +  Type + from tbl1

This is the result I hoping to get.
P1 12341 Open detail1description1
P2 12342 Close detail2
P3 12343 Close description2            
P4 12344 Close

How will I do that?
0
Comment
Question by:tommym121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37733981
This may work...

Select Name + ' ' +  CAST(accountcode AS VARCHAR(10)) + ' ' + Type + RTRIM(ISNULL(' ' + detail, ' ') + ISNULL(' ' + description, '')) from tbl1

Open in new window

0
 
LVL 80

Expert Comment

by:arnold
ID: 37734127
are the empty fields empty or are they null?
select (name + ' ' + accountcode +' '+ type + ' ' + isnull(detail,'')+isnull(description,'') ) from tbl1
if the fields are empty, empty field cat with an empty field leaves an empty field.

This presumes all the data types are similar.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37734673
What is the datatype for AccountCode. If it is a numeric\integer field, you have to cast it to a varchar... If not then just see what field is nullable and while concatinating the field, just check for isnull(fieldname,''). This will help eliminate the side effect of a field being null because anything added to null is always null...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Expert Comment

by:micropc1
ID: 37734680
Arnold, i think you made your post after I edited mine. Look again... I'm using RTRIM to check for blanks and casting accountcode to a varchar.
0
 

Author Comment

by:tommym121
ID: 37734804
The field can be null and empty
The account code is numeric

What if I need to bracket detail or description in the result when they are present, How do you handle it.  Or I need to put a '-' if both detail or description are present.

Is there a programming mechanism like traditional programming language (e.g. 'C') that I can loop through to create a format string I need?
0
 
LVL 80

Expert Comment

by:arnold
ID: 37734836
What is the reason for catcatnating the various items?
You can Output the data as you see fit without going through this complexity on the SQL server.
I.e. run the select columns for table
 Then output the response as you see fit.
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37734871
You could do this with a cursor... a little lengthy, but it can be done...

DECLARE @name varchar(10)
DECLARE @accountCode int
DECLARE @type varchar(10)
DECLARE @detail varchar(10)
DECLARE @desc varchar(10)
DECLARE @detailDesc varchar(100)

CREATE TABLE #tmpTbl
	( val varchar(200))

DECLARE tbl1Cursor CURSOR FOR  
SELECT name, accountcode, [type], detail, description
FROM tbl1

OPEN tbl1Cursor   
FETCH NEXT FROM tbl1Cursor 
INTO @name, @accountCode, @type, @detail, @desc

WHILE @@FETCH_STATUS = 0   
BEGIN   

	--if detail is not null and description is not null
	IF @detail IS NOT NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @detail + ']-[' + @desc + ']'
	
	--else if detail is null and description is not null
	IF @detail IS NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @desc + ']'

	--else if detail is not null and description is null
	IF @detail IS NOT NULL AND @desc IS NULL 
		SET @detailDesc = '[' + @detail + ']'
		
	--else if detail is null and description is null
	IF @detail IS NULL AND @desc IS NULL 
		SET @detailDesc = NULL
	
	--add the item to a temp table
	INSERT INTO #tmpTbl
	VALUES (@name + ' ' + CAST(@accountCode AS VARCHAR(10)) + ' ' + @type + ' ' + @detailDesc)

	FETCH NEXT FROM tbl1Cursor 
	INTO @name, @accountCode, @type, @detail, @desc  
END   

CLOSE tbl1Cursor   
DEALLOCATE tbl1Cursor 

--select all the rows from the temp table and drop it
SELECT * FROM #tmpTbl
DROP TABLE #tmpTbl

Open in new window

0
 

Author Comment

by:tommym121
ID: 37735402
micropc1,

Can it be create a function (FunctionX)  that will pass name, accountcode, [type], detail, description as arguements and will return a string  so we can call this function in the select statement.

Select name, accountcode, [type], detail, description, FunctionX (name, accountcode, [type], detail, description)  from tbl1

Is this possible at all?
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37735558
Yes, but I'm on my iPhone at the moment. I can put something together later tonight when I'm near my computer. You'll basically want to put those IF blocks in a CREATE FUNCTION statement that returns the result as a varchar.
0
 

Author Comment

by:tommym121
ID: 37735617
Micropc1,

Thanks.  I did not mean to get you away from your important phone call :)
I do appreciate you help as I am not proficient in SQL yet.  Thanks again.
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37735627
Lol...no, not on a call...i mean I'm not near a computer and i'm typing this on my iPhone. Kindof hard to do a lot of typing on this small screen. :)
0
 
LVL 7

Accepted Solution

by:
micropc1 earned 2000 total points
ID: 37736142
This should be what you need... run this once to create the function...

CREATE FUNCTION getDetailDesc (@name varchar(10), @accountCode int, @type varchar(10), @detail varchar(10), @desc varchar(20))
RETURNS varchar(100)
AS
BEGIN
	DECLARE @detailDesc varchar(30)
	
	--if detail is not null and description is not null
	IF @detail IS NOT NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @detail + ']-[' + @desc + ']'
	
	--else if detail is null and description is not null
	IF @detail IS NULL AND @desc IS NOT NULL 
		SET @detailDesc = '[' + @desc + ']'

	--else if detail is not null and description is null
	IF @detail IS NOT NULL AND @desc IS NULL 
		SET @detailDesc = '[' + @detail + ']'
		
	--else if detail is null and description is null
	IF @detail IS NULL AND @desc IS NULL 
		SET @detailDesc = NULL

	RETURN @name + ' ' + CAST(@accountCode AS VARCHAR(10)) + RTRIM(' ' + ISNULL(@type, '')) + RTRIM(' ' + ISNULL(@detailDesc, ''))
END;

Open in new window


then use use it like this...

SELECT name, accountCode, [type], detail, [description], dbo.getDetailDesc(name, accountCode, [type], detail, [description]) AS detailDesc FROM tbl1

Open in new window

0
 

Author Closing Comment

by:tommym121
ID: 37741910
THanks
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question