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


ADVERTISEMENT

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

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

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

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 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

Removing Rows That Contains Long String

Mar 23, 2004

Anyone know how to check the length of a varchar data in a table? I want to delete rows that contains an entry that exceeds 100 characters.

I should be able to write a program to do it, but I am wondering if I can do it in SQL.

View 2 Replies View Related

Removing Partial String From Column

Oct 11, 2005

how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the front

View 5 Replies View Related

Removing All End Of Line Character From A String

Aug 6, 2007

Hello all,

Im looking for an efficient way to remove all end of line character from a string.

Is there a function to do that or to replace them with another character?

View 1 Replies View Related

Updating The String Removing The Strings.

May 20, 2008

I have to write a query to update the table to remove dots .... from the string value.
so i could write.

UPDATE tbaddress
SET Title='New address'
WHERE Title='New address....'

but, there could be more than this specific record in the table, so i have to find each single record to do this. Is there a way to update the table so I can just remove the .... but rest of the text remain.

thanks

View 2 Replies View Related

Need Help Removing String Data From Text

Apr 4, 2008

I was wondering if someone could tell me how to use an SQL Function to remove everything from the mess of characters below except 'Test.' Really, any message could be substituted, so it's not as easy to leave everything except the 'Test.' string.


{
tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fnilfcharset0 MS Shell Dlg 2;}{f1fnil MS Shell Dlg 2;}}
{colortbl ;
ed0green0lue0;}
{*generator Msftedit 5.41.15.1507;}viewkind4uc1pard x720cf1f0fs20 Test.f1par
}


It's RichText.

The fs20 could be fs24, fs25, etc. (any numbers). The end of the number would be the point to trim the beginning off.

The '' seems like it would be the point to start trimming the end off.


I've been experimenting with SUBSTRING together with CHARINDEX, but am just not getting even close to coming up with a solution.

Any help would be greatly appreciated


Thank you.

View 1 Replies View Related

Removing Special Characters From A Number String.

Jul 11, 2001

Hi,

I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.

Thanks, Mark

View 1 Replies View Related

Removing Or Replacing Part Of A String In A Field?

Mar 4, 2008

Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:

- A Table called: Table_1
- A Field called: MyField
- MyField contains the value: Hello I am on the msdn forums

Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?

Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.

Appreciate the responses, Onam.

View 5 Replies View Related

SQL Server 2012 :: Removing Character (Letters Only) From A String

Dec 19, 2014

I need to build a query, or use a function to removing alfabetic Character from a string.

My string is
WI14000323-0003

The value i want to keep is : 14000323-0003.

I try using this SELECT:

SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable

but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.

View 2 Replies View Related

SQL 2012 :: Removing Consecutive Characters From Middle Of A String

Apr 14, 2015

I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?

Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.

I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.

View 9 Replies View Related

SQL Server 2012 :: Removing Greek Characters From A String

Sep 17, 2015

I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.

DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters

In this case, the "α" is being replaced, but so are "a" and "A".

I have tried changing the datatype from varchar to nvarchar and also changing the collation.

View 4 Replies View Related

SQL Server 2012 :: Removing Duplicate Character From String

Oct 18, 2015

How to write a function to remove duplicated characters only if they come in sequence.

Examples

darrk should return dark

eeagle should return eagle

redd should return red

corner should corner as it is as the r's are not in sequence.

View 9 Replies View Related

Integration Services :: SSIS Is Removing Leading Zeroes From 6 Character Time String?

May 29, 2015

I am using SSIS 2012 SP1 to import a comma delimited csv file into a SQL table.

One of the fields carries a time value:

Source = textfile, column=DT_STR(8), value format = "hhmmss", e.g. "011525"
Destination = field in SQL table, data type = time(0)

To get it from the textfile to the SQL table I am:

1.) Creating a derived column called [d_Time of Entry]with the following formula -

SUBSTRING([Time of Entry],1,2) + ":" + SUBSTRING([Time of Entry],3,2) + ":" + SUBSTRING([Time of Entry],5,2)

2.) Performing a data conversion task to convert [d_Time of Entry] from DT_STR(8) to time(0) The upload fails because values that start with a zero, i.e. times before 10am, have their leading 0's stripped before being derived.  You can see this because "011525" is derived as "11:52:5" when it should be "01:15:25".

View 10 Replies View Related

Removing Non-numeric Characters From Alpha-numeric String

Oct 24, 2007

Hi,

I have one column in which i have Alpha-numeric data like

COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.

I want to remove Non-numeric characters from above (space,'(',')',+,........)

i want to write something generic (suppose some function to which i pass the column)

thanks in advance,

Mandip

View 18 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

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

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 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

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

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

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







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