How to convert int to text for single string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to convert int to text for single string?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-08 : 14:33:13

I wish to return a list of selected integers as a single string. So instead of getting

24
36
48

I get 24/36/48.

My select query:

SELECT CONVERT (varchar(10), quantity) + '/' FROM flexing_stock_transactions WHERE item = 'CH' AND week = 35 GROUP BY quantity ORDER BY quantity

returns

24/
36/
48/

How can I achieve my goal please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-08 : 14:42:01

Using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-08 : 14:45:06

Ooops, sorry I should have stated it was SQL Server 2005 in my initial post.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-08 : 15:56:03

OK, I figured it out. My solution:

SELECT retail, supplier, count = dbo.fn_concat_boxes('CH', 35), total
FROM flexing_stock_transactions
WHERE (item = 'CH' AND week = 35 AND firm_or_commission = 'C')
GROUP BY retail, boxes, supplier, total
ORDER BY retail DESC

Using function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(100)

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity

RETURN @Output

END

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-08 : 22:44:11

using FOR XML PATH is generally faster in doing the concat than this method.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-09 : 06:12:52

I see. Any chance of an example please? I am a SQL newbie and have no knowledge of this.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 12:35:23

http://weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-09 : 15:18:59

>>using FOR XML PATH is generally faster in doing the concat than this method.

Is it? Has anyone tested the performance?

- Jeff
http://weblogs.sqlteam.com/JeffS

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 15:41:46

in my experience it is. this seems to say the same:

http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 01:28:06

quote:
Originally posted by jezemine

in my experience it is. this seems to say the same:

http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html

elsasoft.org

Also refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-10 : 08:23:47

Many thanks guys.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-10 : 09:36:44

Good links, thanks guys. Of course repeated ISNULL/COALSESCE/CASE calls will not be as fast as possible, the fastest/best technique is to simply concate and then return only a substring. Doesn't madhivanan's link confirm this? Or am I missing something? It appears to be faster to use a UDF rather than than XML if you do it efficiently.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources