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.
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
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
Hi All,
Is there a way to remove Leading & Trialing Commas (,) in a string using SQL code ? Appreciate any thoughts.
Thanks
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
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
I want to remove the default value of RS Parameter (Value:"<Select a Value>") but don't know how. Is anyone here can help me out?
View 1 Replies View RelatedI'm executing the following...
select COL1, min(COL2) from TABLE group by COL1
the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.
I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"
would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?
this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.
tia
a
Hello All,
I am developing reports using SQL Reporting Services 2005. These reports have some single valued parameters and these parameters will be provided in report viewer itself. These parameters should have "All" as default.
"Select All" comes by default in Multivalue report parameter. But for single value parameter it always shows "<Select a value>" and if the user does not select a value,it prompts for one.I need to remove <Select Value> and show "All" as default thereby user does not have to select a value.
I can include "All" as a part of the select query in the parameter's datasource so that it can be shown in the parameter dropdown . However, I am not sure how I can remove "<Select a value>" from the dropdown.
Does anyone have any pointers? Thanks in advance.
Ashish
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.
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
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.
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.
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
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 RelatedI 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"
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 RelatedHey 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.
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.
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.
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 RelatedHow 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
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 RelatedHI
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
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
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 RelatedHow 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!
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".
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.
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 RelatedIs 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'
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,
Hi! I am trying to remove an "*" I used to transfer a file from Excel to SQL. I put the "*" in the NULL values so that I would not get an error. Now that I have the file in SQL, I need to remove the :*" from some columns in my table. How can I select all of those in a column and delete them without deleting each individual one. Any ideas?
Thanks for your help.
Scott