How To Concatenate Several Fields Together With Commas

Nov 17, 2014

I have a hierarchy of product categories and I want to string them together to show the complete path (breadcrumbs) to the item. Each category/subcat must be separated buy a comma. The catch is that not all items have the same number of cats/subcats. Here is what I am currently doing, but as you can see, this results in extra commas where the subcats are null.

Code:
create table #ItemCat(
itemNo int,
Cat varchar(50),
SubCat1 varchar(50),
SubCat2 varchar(50)

[Code] ....

Is there some way to concatenate these, separated by commas, but ignoring the NULL fields? For example, ItemNo 1 should show "Kitchen, Appliances"

View 3 Replies


ADVERTISEMENT

Management Studio Export To CSV Does Not Delimit Commas In Text Fields

Mar 29, 2007

Is there any nice way when saving a result set grid to CSV (right click, save to CSV) to properly delimit text fields that contain commas? As it is, text data that includes commas cannot be safely exported to CSV.



Example:



SELECT 'Hi there, friend!', 'Hi' UNION ALL

SELECT 'Bye now', 'Bye'

View 14 Replies View Related

Concatenate 2 Fields

Feb 13, 2008

Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks

View 7 Replies View Related

Concatenate Two Text Fields

Mar 29, 2006

New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:

Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....

I've created the following query that displays the columns:

CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE

FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID

WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)

The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE

Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?

Please advise - thanks!

View 8 Replies View Related

Concatenate Two Fields In A View?

Feb 14, 2006

Hello,I'm wondering if there is a way to concatenate two fields or a field and astring value in a single field in a view?Where in Access I might write;[field1] & " (m3)" as TotalVolumeis there a way to do this in an SQL Server View?Thanks!

View 2 Replies View Related

Concatenate Text Fields In Query

Oct 11, 2005

I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.

For example


Code:

SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'

FROM Table1



However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.

View 2 Replies View Related

Concatenate Fields In Stored Procedure

Feb 22, 2008

Does anyone have code example on how to concatenate fields in a SQL Server stored procedure?
I would like to create a stored procedure to concat (off_name ' ' off_fname ' ' off_prior) to a field to bound to a combobox.
Thanks,
Ron

Table below.


ALTER PROCEDURE CreateOfficersTable

AS

CREATE TABLE [officers](

[off_int_id] [int] Identity Primary Key,

[off_badge] [int] NULL,

[off_lname] [nvarchar] (20) NOT NULL,

[off_mname] [nvarchar] (20) NOT NULL,

[off_fname] [nvarchar] (20) NOT NULL,

[off_radio_num] [int] NULL,

[off_handle] [nvarchar] (10) NULL,

[off_unit] [nvarchar] (5) NULL,

[off_prior] [int] NULL,

[off_shift] [int] null,

[dateadded] [datetime] NULL,

[userchange] [nvarchar](10) NULL,

[changedate] [datetime] NULL

)

View 5 Replies View Related

Concatenate Text And Varchar Fields

May 29, 2008

I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:


UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '

' WHERE Column_TEXT = 'Some text'

I also tried

UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'

But I keep getting the error

The data types text and varchar are incompatible in the equal to operator.

Help!!

Thanks in advance
Mangala

View 3 Replies View Related

Transact SQL :: Concatenate Multiple Fields Into One

Dec 5, 2015

I have a table where I need to concatenate all values into one field separated by a comma.  If the field is null display a blank value.  This is my table structure and example output

Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)

[code]...

View 5 Replies View Related

How To Concatenate Two Fields In A Textbox One Integer And Other Charecter Field

Jul 2, 2007

I have the folliwing two fields, want tp concatenate:



=Fields!sequenceno.Value & =Fields!LogType.Value



Thank you very much for the information.

View 3 Replies View Related

SQL 2012 :: Concatenate Fields And Insert Comma Between - How To Handle Nulls

Sep 5, 2014

I thought it's easy but somehow got lost, what is the good way to insert that Comma intelligently, depending if any of var value is NULL.

-- DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = 'bravo2', @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'
DECLARE @var1 varchar(10) = 'alpha1', @var2 varchar(10) = NULL, @var3 varchar(10) = 'charlie3',@var4 varchar(10) = 'delta4'

SELECT ss= (CONCAT(@var1, + iif(COALESCE(@var1,@var2,@var3,@var4) IS NULL,'',', '),
@var2, + iif(COALESCE (@var2,@var3,@var4) IS NULL,'',', '),
@var3, + iif(COALESCE( @var3,@var4) IS NULL,'',', '),
@var4) )

View 3 Replies View Related

Problem With Updatetext In SQL 2000 Trying To Concatenate Multiple Text Fields

Jan 2, 2007

Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan

View 4 Replies View Related

Query Using Commas

Mar 12, 2007

Hi,
I have table Article(ID,Title,FAID)
I need a query that will select all the Article.ID records where the FAID contains the number of the article ID
For exapmle,  Article Table content is:

1, "Title1","2,6"
2, "Title2",""
3, "Title3","6,1"

6, "Title6","2"
Lets say I want to get all titles of article ID 1. I am going to its FAID which is  "2,6"
So the query will return : "Title2", "Title6"
 
Can you advice how to write this? I can do a walk around solution where I will open a new table name FA but I rather not to.
 

View 3 Replies View Related

Missing Commas

Feb 18, 2008

I have a normal nvarchar(50) field in a table that has a single comma in it but when i read it using OleDbDataReader the comma gets removed. 
any ideas what i am doing wrong ?
 
many thanks

View 4 Replies View Related

Commas In Excel

May 10, 2008

Hi

I have an excel file with 2 columns:
article art_group
kb art,tp,key
portal grup,port,li

I have a table in the database called tb_artGroups with the columns

article art_group

Now for each row in the excel i have to write the values after the comma in a new row such as:
article art_group
kb art
kb tp
kb key
portal grup
etc...

How can i do that with SSIS?

Thanks





Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 4 Replies View Related

Commas In The Result Set

Sep 4, 2007



I am using SQL 2005 and have a query that returns seven columns of data. In one the columns, the results contains commas. For example, one of the results is 'brooke, nick, dustin, and me.jpg'. The results are saved into a .csv file so it can be imported into Excel. The user named the file with the commas and I need to preserve the original file name.

My question:
Is there some way to format the data inside of SQL so that when it gets saved as a .csv file, the extra commas don't throw off the column layout?

I don't want to have to manually clean up possibly several hundred rows of data everytime I run the query.

Thanks in advance.

View 4 Replies View Related

Removing Commas(,) In A String

Mar 7, 2000

Do anyone know of a function, that I can use to removed commas
from a string? eg. "The lady, cross the road" should be "The lady cross the road"

Thanks, Vic

View 2 Replies View Related

BCP And Commas/double-quotes

Jul 30, 1999

Hello all,

I am using SQL Server 6.5 SP5a.

I have to use bcp to import two text files everyday for database update. The problem is that some of the character fields that are being imported have double-quotes and/or commas in them. When these are imported into the SQL Server tables additional double quotes are being added into these strings.

Example:
INCOMING STRING = a"a
IMPORTED STRING = "a""a"

INCOMING STRING = b,b
IMPORTED STRING = "b,b"


I have searched through BOL and have not been able to find any information.

Does anyone know what is causing this and if so how to correct it?

Thanks,

Bryan Ziel

View 3 Replies View Related

Format Numbers With Commas

Jul 4, 2007

Hello Nice simple T-SQL question that will no doubt prove totally uncontroversial :) Anyone know the easiest way to get from:123456789to123,456,789in T-SQL? Currently I cast my int as money, use convert to varchar with style 1 and then lop off the .00. Is there an easier way? Two things before you get all upset and start squealing about front ends:1) These are admin functions that I am running from SSMS. I don't want to go to the bother of creating an application when SSMS is perfectly adequate for what I need (namely to-a-large-degree unformatted result sets).2) I'm not too fussed about it just curious. Ta & tra la!

View 2 Replies View Related

Removing Commas From A Select

May 15, 2008

I'm trying to export a query to a csv file. Everything works great
until I hit the name field. Is there a way to remove commas from a select statement.

Here's what my Data looks like

Item # | Name
-------------------------
1111 | Acme, Inc
1112 | Test Company, Inc


I'd like it to print out as

Item # | Name
-------------------------
1111 | Acme Inc
1112 | Test Company Inc

Thanks for any help in advance.

View 1 Replies View Related

Formating Numbers With Commas

Jul 23, 2005

I am pulling several numbers from a SQL table, adding them and doingvarious calculaitons. The numbers do not display a comma to separatethousands. What is a way to format this?

View 5 Replies View Related

Ordeering Data Separated By Commas

Jan 1, 2008

Hey everyone. I've never posted on this site before so if I miss anything, please let me know.

I need to order the following data

18
14,15
13.2
14.2
15,16
15
0
14.1
12
6
15,16,18
11
16
15,17
13
14
14,15.2
17

into this order:

0
6
11
12
13
13.2
14
14.1
14.2
14,15
14,15.2
15
15,16
15,16,18
15,17
16
17
18

Because the data contains commas, I've had to change the data type to varchar. I've seen several instances of PATINDEX used however I have not ran across any of them that would put the above in the order in which I need. Btw, I have about 20 columns with this type of data and I eventually plan to use the results of each column to populate dropdownlists . Thank you all in advance.

View 11 Replies View Related

T-SQL (SS2K8) :: Removing Commas In Column

Feb 5, 2015

I have a simple statement where I am trying to remove all commas that may be in the xn_workordeld column.

SELECT
xn_workordeld = case replace(xn_workordeld, ',', '') as "WORKORDER LD"
from workorder

View 3 Replies View Related

Selecting Data Seperated By Commas

Jun 3, 2008

Have a column in my DB called _Venue, which has been populated with address information separated by commas, like this:

Address1,address2,address3,address4

The address has sometimes only a few lines, for example I may have just Address1 or just address1 and address2 filled etc.

I need to write a query that will individually select addressX, so I can pick and choose what part of the full address to use.

Can these be done?

Thanks in advance.

View 9 Replies View Related

How To Find All Commas Positions In The String?

Mar 24, 2006

Hi,

Can you help

I have a string that has multiple commas:
'AAA,BBB,CCC,DDD,EEE,...'
I need to pick any characters between commas or find all commas positions.

View 4 Replies View Related

Extra Commas In Flat File

Jul 13, 2007

I am trying to create a SSIS package with a csv flat file for the both the destination and source. I cannot control the source file. I need to be able to handle an extra comma in the source file which occasionally contains suffixes i.e. {John, Smith, Jr,} which could cause some records to be 9 fields and others 10. Any ideas?

View 1 Replies View Related

Delete Commas And Decimal Place

Dec 19, 2007

How can I convert a field with commas, cents and decimal to a numeric without commas and decimals and cents...?

Sample nvarchar values to desired output (integer whole value, truncated or rounded doesn't matter):
0.00 to 0
14,000.04to 14000
139,234.31to 139234
14,017,300.00to 14017300

View 2 Replies View Related

Removing Leading And Trialing Commas

Mar 7, 2008

Hi All,

Is there a way to remove Leading & Trialing Commas (,) in a string using SQL code ? Appreciate any thoughts.

Thanks

View 10 Replies View Related

Formatting Numbers With Commas In TSQL

Jul 20, 2005

Once I've converted my floats to chars using STR, is there an easy wayto put commas in separating the thousands.i.e. convert53000000.12to53,000,000.12I'm thinking I'll have to do it with a user defined function and thevarious string functions myself but was wondering if anyone had aneasier way?CheersDave

View 6 Replies View Related

How To Eliminate The Commas From The End In Sql Query When The Column Value Is Null

Jun 26, 2007

HI
I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like
select ISNULL(dbo.tblperson.Email1, N'') + ';' + ISNULL(dbo.tblperson.Email2, N'') + ';' + ISNULL(dbo.tblperson.Email3, N'') AS Email from tablename.
One eg of the output of the above query when email2,email3 are having null values in the table is :
jacky_foo@mfa.gov.sg;;
means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column.
Please let me know how can i do this

View 6 Replies View Related

Putting Commas Between Select Statement Values

Jan 12, 2005

Hello,

This may be a strange request, but I am going to ask about it anyways.

Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this:

NUMBERS
1
2
3
4

How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get:

1,2,3,4

Instead of:

1
2
3
4

Any ideas?

Thanks

View 1 Replies View Related

Removing Commas From Column - Now It's A Mile Long

Jun 24, 2007

Hello,

I recently used the REPLACE command, as described in a previous topic on this forum, to remove unwanted commas however I've now got a new problem, the column has become half a mile long. I was asked to raise a new topic and give examples, see below:

CAN ANYONE TELL ME:
1. Why is the column now bigger?
2. How can I redue the size of the column to it's origional size?

I have already attempted to use CONVERT, RTRIM and CAST around the replace command, all give an error.


Example query and result before REPLACE:
select ICMAFinInstName,CptyCode from tradedetails

ICMAFinInstName CptyCode
---------------------------------------------------------------------- ----------------

Example query and result using REPLACE:

select replace (ICMAFinInstName,',',' ')AS NoCommaInst,CptyCode from tradedetails

NoCommaInstrument CptyCode
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------

(I see in the preview that this forum has removed the spaces between the titles, but the dashes (--) show the size of the columns

Thanks

JM

View 20 Replies View Related

How To Split Names Seperated By Commas And Rejoin Them...

Mar 11, 2008

I use SQL Server 2000.

I have some names stored in a table in the following format...

eg
"Royales, Jon"
"Smith, Alan"
"Jones, Tom"

what I would like to see is...

"Jon Royales"
"Alan Smith"
"Tom Jones"

I assume I should search the string for a comma (,) and use split and then rejoin the values. I need to do this as part of my select statement... is it possible?

I have searched the forums for similar posts but didn't find anything helpful.

Jon

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved