SQL select statement string concatenation

Can something like this be done with a select statement:

SELECT col1, concat(col2 + ' ') FROM .... GROUP BY col1

I know i can use count(col2) or sum(col2) for integers but is there a function for concatenating if the type is nvarchar or nchar?

-------------Problems Reply------------

In SQL Server, if you want to concatenate across rows, there is no built in function to do this.

I personally like using XML PATH as it seems to perform well, but this will work only in SQL Server 2005 onwards

SELECT
STUFF(
(
SELECT
' ' + Description
FROM dbo.Brands
FOR XML PATH('')
), 1, 1, ''
) As concatenated_string

The + operator is used to concatenate strings in T-SQL.

EDIT:

If you wish to aggregate strings over multiple rows this might help.

Using Sql Server there is no built-in aggregate concatenation foncton, I know MySql has one called group_concat.

Sql Server, you, would either have to write your own scaler funcion, or a CLR function to achieve this.

Or you can use a cursor to do this for you, with a table var to return the results. I can provide an example if you like.

Category:sql Views:0 Time:2009-12-05

Related post

  • T-SQL - Select records into concatenated text? 2009-04-15

    I'm trying to investigate when & why certain rows are getting deleted in a SQL 2005 database. I've started building a trigger to log some information when a row is deleted. My trigger is activated when row(s) are deleted from a certain table. I h

  • Sql select to string 2010-06-30

    I have a query that returns a result set of one column, but I want it to be converted into one long string for use as a subquery. I.E. I want to be able to do: SELECT user.*, (SELECT group_name FROM user_groups WHERE userID = user.ID) AS Groups FROM

  • SQL select row into a string variable without knowing columns 2010-02-24

    I am new to writing SQL and would greatly appreciate help on this problem. :) I am trying to select an entire row into a string, preferably separated by a space or a comma. I would like to accomplish this in a generic way, without having to know spec

  • Database-independant SQL String Concatenation in Rails 2010-06-06

    I want to do a database-side string concatenation in a Rails query, and do it in database-independent way. SQL-92 specifies double-bar (||) as the concatenation operator. Unfortunately it looks like MS SQL Server doesn't support it; it uses + instead

  • T-SQL String Concatenating multiple rows 2011-08-09

    Possible Duplicates: Simulating group_concat MySQL function in MS SQL Server 2005? Concatenate values based on ID I am trying to write a SQL query in SSMS 2008 to group by a single column. I have a table with just two columns: Part Number and Product

  • Stored procedure without string concatenation SQL Injection vulnerabilities 2012-01-06

    I'm using SQL Server 2005/2008, I have a stored procedure that doesn't use string concatenation for generating an EXEC statement, but it does use a dynamic name for the stored procedure. I think the @stored_procedure_name and potentially the @object_

  • String concatenation in SE 4.1 SELECT statement 2012-03-22

    I'm using ISQL-SE 4.1 and need to concatenate two CHAR strings in a SELECT statement. I tried using the two pipe symbols || but that doesnt work in 4.1 is there another way to do the trick, maybe using subscripts [a,b] or using a temporary file witho

  • String concatenation operator in Oracle, Postgres and SQL Server 2009-09-03

    Is there a way to have a common operator for concatenation in Oracle, Postgres and SQL Server. In Oracle we uses '|', postgres uses '||' and sql server uses '+'. I've solved the problem in postgres by adding the custom operator '+' to support string

  • String concatenation within INFORMIX-SQL "Perform" screens 2010-03-07

    How can I concatenate two char columns within a perform screen? example: table sample col1 char(1), col2 char(1) . . after editadd of sample let label_3 = sample.col1 + sample.col2 .. this didn't work, I even tried using subscripts for the 2 cols but

  • Using VARCHAR(MAX) with string concatenation in SQL Server 2005 2010-12-28

    A user of one of our databases is trying to submit an UPDATE query to a SQL Server 2005 database and the text is being truncated unexpectedly. The field being truncated is VARBINARY(MAX) and is used to store HTML text. The query is roughly: UPDATE St

  • How do select a string greater then 8k characters out of a SQL Server 2008 XML field 2011-04-06

    I am building a small php application that simply extracts an XML string out of a database and displays the entire string on a page inside a textarea. Sounds simple, right? When i do this, i hit an 8k character limit and my XML is truncated. All i wa

  • A macro to convert a SQL query to a string concatenation and viceversa? 2011-09-07

    I'm working with Access and I have many queries in the code like "SELECT something, something " _ & "FROM the_table " _ & "WHERE something Is Null " or "SELECT " & _ "Min(something), " & _ "Max(something2) " & _ "FROM (the_table "

  • How to perform string concatenation in PL/SQL? 2010-10-20

    I have a variable defined as define dbs '&1' Suppose I pass database1 as an argument. Then the statement is interpreted as define dbs database1 I want to append single quotes around the string, ie I want it to be interpreted as define dbs 'databa

  • Problem in string concatenation in sql server using FOR XML Path. 2011-06-16

    I have the below data UniqueID ID data 1 1 a 2 1 2 3 1 b 4 1 1 5 2 d 6 2 3 7 2 r The expected output being ID ConcatData 1 a,-,-,b,- 2 d,-,-,-,r What we have to do is that, the number of numeric charecters has to be replaced with those many dashes('-

  • What is the string concatenation operator in Oracle? 2008-11-10

    What is the string concatenation operator in Oracle SQL? Are there any "interesting" features I should be careful of? (This seems obvious, but I couldn't find a previous question asking it). --------------Solutions------------- It is ||, for example:

  • Removing aliases from a SQL select statement, using C# and regular expressions 2010-07-08

    I’m learning regular expressions and I’m playing a little with them. I proposed myself an exercise in which I have a method that removes the columns aliases in a SQL Select statement. This should work like this: The method can remove aliases in a SQL

  • C#: most readable string concatenation. best practice 2010-07-18

    Possible Duplicate: How should I concatenate strings? There are several ways to concat strings in everyday tasks when performance is not important. result = a + ":" + b result = string.Concat(a, ":", c) result = string.Format("{0}:{1}", a, b); String

  • Sql select query help 2010-08-26

    I have a database table with data as shown below: Primary key | Column1 | Column2 | col3 | col4 |col5 --------------------------------------------------------------------- 1 | Chicago Bulls | Michael Jordan | 6'6 | aaaa | cccc 2 | Chicago Bulls | Sco

  • Which is the best method to perform string concatenation in PHP? 2010-11-30

    In php we can append strings in many ways. Method 1 ---------------------------- $sql = "SELECT field1, "; $sql .= " field2, "; $sql .= " field3, "; $sql .= " field4, "; $sql .= "FROM table1 "; $sql .= "WHERE condition1 = '".$value."' " ; Method 2 --

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.154 (s). 11 q(s)