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
ADVERTISEMENT
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
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 3, 2015
I have what seems to be a common problem where I have names like "Last, First" that I need to be able to select using a multi-value parameter, which of course, SSRS passes as comma-delimited and gets messed up. I need to find a way to use a different delimiter or pass CHAR(44) instead of the comma, or something. Even worse, some people have suffixes and multiple commas in one name!
View 4 Replies
View Related
Oct 4, 2006
How to display money type data with commas every 3 digits?
I tried :
CONVERT (money, ProjectCost, 1) AS ProjectCost
but it gives me the type as 1234.56 no commas in it?
can anyone help me with that?
thanks!
View 4 Replies
View Related
Mar 20, 2006
If I pull a value from a MSSQL field with is defined as money, how can I get it to display in a textbox with commas and NO decimals?
87000.0000 = 87,000
I can currently remove the decimals like below but is there a way to add the commas as well?
decRevenue = drMyData("Revenue")
txtRevenue.Text = decRevenue.ToString("f0")
It current shows "87000".
View 1 Replies
View Related
Jul 9, 2015
All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.
Data in Tables
Test1, Test2, Test3
Test4;Test5;Test6
Desired output
Test1
Test2
Test3
Test4
Test5
Test6
Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.
View 7 Replies
View Related
Mar 9, 2011
I have a table that I am exporting via a SQL server table to a csv file, via the flat file connection manager within SSIS. My issue is that one of the fields in my table contains "," (commas), so when the file is created it creates new columns in the csv file because the field is "," comma delimited on the column. Is there anyway to get round this without having to get rid of commas in my table (which I've tried and works)?
View 12 Replies
View Related
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
Sep 1, 2011
We have recently upgraded to SP1 of SSRS 2008. As a result, when we export a blank report to CSV, we now get a line of commas below the headings. Or found a way to not include the commas?
New SSRS Output
Portfolio_Reference,Portfolio_Name,R,TR,TD,TC,D,
Old SSRS Output
Portfolio_Reference,Portfolio_Name,R,TR,TD,TC,D,
View 4 Replies
View Related
Jul 20, 2005
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies
View Related
Dec 26, 2007
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
View 5 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
