Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Replacing ';' with ',' in test field

I have a field with a string of email addresses :
"1@1.com;2@2.com;3@3.com"

I need to replace the ';' with a ','
"1@1.com,2@2.com,3@3.com"

I have lots of data to deal withso need a sql solution.

can this be done with sql?

Thanks, Tom.
0
boardtc
Asked:
boardtc
  • 2
  • 2
  • 2
  • +3
2 Solutions
 
Kevin3NFCommented:
REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

Examples
This example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx')
GO

Here is the result set:

------------
abxxxfghixxx
(1 row(s) affected)

0
 
rafranciscoCommented:
UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, ';', ',')
0
 
Saqib KhanSenior DeveloperCommented:
update Table
set field_name = Replace(Firld_NAme, ';', ',')
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
lluthienCommented:
of course this will result in a mess if you have actual data containing either an comma or a semi-colon

but you probably know that
0
 
boardtcAuthor Commented:
Thanks guys, unfortunately the column in question is a Ntext column and  this will not work with the replace command...
0
 
lluthienCommented:
bummer..

what does the clientside look like?
0
 
Jim P.Commented:
A possible solution if your data is less than 8000 characters:

UPDATE TableName
SET Column = REPLACE(CAST( Column AS VARCHAR(8000)),';',',')
WHERE  LEN(Column) < 8000
GO

And to find out if you need to do any manually or some other workaround such as a temp table then:

SELECT Count(Column)
FROM TableName
WHERE  LEN(Column) > 8000

Just my $0.02. :)
0
 
boardtcAuthor Commented:
Wcked stuff man, thank you :-)
0
 
Jim P.Commented:
You're welcome and have a good day.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now