Click here to Skip to main content
14,238,591 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to get Id and Text value from database in such a manner that both are returned as a single column with values separated by some separator. I'm thinking of using '$' sign as separator of values. eg: Suppose if Id is 10 and Text is 'abc', in my current way value will be returned as '10$abc'. I want to know is there any standard of using some specific operators to combine values or one can use any separator one desires?;


What I have tried:

Suppose if Id is 10 and Text is 'abc', in my current way value will be returned as '10$abc'. I want to know is there any standard of using some specific operators to combine values or one can use any separator one desires?;
Posted
Updated 25-Sep-17 0:00am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

There is no standard way to do so, because in most of the cases it is a bad idea to do string manipulation in SQL...
However you can use CONCAT[^] to do so easily (for a fixed list of columns):
declare @sep as nchar(1) = '$'
select
  concat(col1, @sep, col2, @sep, col3, @sep, col4)
from
  table1

(you may need some casting, depending on the column data types)
   
Comments
knackCoder 25-Sep-17 6:41am
   
So as you just said, there is no convention available to use specific operators only while appending values in a single column.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100