
	ASP Shopping Cart, Forum & BBS - all for $20 from CodeToad Plus!

Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || RAM

Search Site:

Home » ASP » Article

Concatenate strings in sql

Article by:

Jeff Anderson ( 1362 ) (4/8/2003)

	Add to Favourites
	Tell a friend

Summary:

A brief introduction to concatenating strings in an sql query (using SQL server or access databases).

Viewed: 392849 times

Rating (118 votes):

2 out of 5

Rate this Article

Read Comments

Post Comments

Concatenate Strings in SQL

Sometime you may need to add a bit of text to a query result on the fly - as you query, you add the text.

This is called concatenating a string - literally meaning 'sticking together'.

It's really quite easy to do. Let's say we have a record in the products table for 'washing machine' with an product_id of 35. There are 5 of them in the quantity field and the product_price per item is $3.25. Here's the query we could use:

Select All Code

select quantity + ' ' + product_name + 's at the excellent price of $' + product_price + '.' as strProductText from products where product_id=35

the recordset set now contains one field called strProductText which contains:

5 washing machines at the price of $3.25.

Outputting to an ASP page

To display the result in an asp page all you need is

Select All Code


<%sql="select quantity + ' ' + product_name + 's at the excellent price of $' + product_price + '.' as strProductText from products where product_id=35"

'open the connection
set objconn=server.createobject("adodb.conncection")
objconn.open ("your_dsn")

'open the recordset
set objrs=server.createobject("adodb.recordset")
set objrs=objconn.execute(sql)

'write the result to screen.
Response.write objrs("strProductText")
%>

As you can see it can be an effective an quick way of producing a full sentence. Notice we added the 's' after the product name ( you could ensure earlier that it was quantity was 1 before adding this!) and that we also added the dollar sign in front of the price - all available as part of the returned field in our recordset.

Concatenating in MS ACCESS

The process is almost identical if you're using an Access database - the only difference is you need to use the & sign instead of the + sign.

Useful Links

CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums

Rate this article: Poor Excellent

View highlighted Comments

User Comments on 'Concatenate strings in sql'

RELATED ARTICLES
ASP FilesystemObject by Jeff Anderson An introduction to the Filesystemobject

ASP GetTempName by Jeff Anderson Use the GetTempName method to create a randomly generated temporary file on the server.

ASP Format Date and Time Script by Jeff Anderson An ASP script showing the variety of date and time formats possible using the FormatDateTime Function.

ASP OpenTextFile by Jeff Anderson An introduction to the OpenTextFile Method of the FileSystemObject

Email validation using Regular Expression by Jeff Anderson Using regular expression syntax is an exellent way to thoroughly validate an email. It's possible in ASP.

Add or Subtract Hours in SQL or ASP using DateAdd by Jeff Anderson A beginners guide to using the SQL DATEADD function to add or subtract hours. Particularly useful when setting the time displayed on the ASP page to a different time zone (eg when the server is in the US, and the site is for a UK audience).

The asp:radiobutton and asp:radiobuttonlist control by David Sussman, et al In HTML, radio buttons are used when we need to make multiple sets of choices available, but we want the user to select only one of them.

The asp:checkbox and asp:checkboxlist control by David Sussman, et al Checkboxes are similar to radio buttons, and in HTML, they were used to allow multiple choices from a group of buttons.

Concatenate strings in sql by Jeff Anderson A brief introduction to concatenating strings in an sql query (using SQL server or access databases).

ASP FileExists by Jeff Anderson An introduction to the FileExistsMethod of the FileSystemObject

Recent Forum Threads

•	Run a program both on windows and linux
•	VERO.SurfCAM.v2014
•	Schlumberger.Petrel.V2013.2
•	Petrel.V2013.2
•	Altair.HyperWorks.v12
•	VoluMill.v6.1
•	VoluMill.NEXION.6
•	VERO.SurfCAM.v2014
•	Schlumberger.Petrel.V2013.2

Recent Articles

	ASP GetTempName
	Decode and Encode UTF-8
	ASP GetFile
	ASP FolderExists
	ASP FileExists
	ASP OpenTextFile
	ASP FilesystemObject
	ASP CreateFolder
	ASP CreateTextFile
	Javascript Get Selected Text

privacy - contact - advertise - hot links - link to us - submit your article