Solved

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

Posted on 2012-03-17
13
434 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
  • 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 76

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
 
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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now