3

I need to export data from SQL and import into SAS. The address field has ',' in the middle of the string. I tried using CSV and tab delimited, but each time SAS breaks up the address field due to the ','.

I tried replacing the comma with a space using code from another question, but it did not work:

 update #temp2
 set STREETADDRESS_e = REPLACE(STREETADDRESS_e ,","," ")

I thought if I put the address string in quotes, this would solve the problem, but my code is not working:

 update #temp2
 set STREETADDRESS_e = ("'" + STREETADDRESS_e + "'")

This seems like it must be a very common problem but I have not found any working solutions...

jpw
  • 44,361
  • 6
  • 66
  • 86
vfiola
  • 79
  • 1
  • 1
  • 6
  • I tagged the question as SQL Server based on syntax, if that's wrong please say. – jpw Oct 13 '14 at 22:52

1 Answers1

7

If you want to surround the string with single-quotes you have to escape them like this:

update #temp2 set STREETADDRESS_e = ('''' + STREETADDRESS_e + '''')

or

update #temp2 set STREETADDRESS_e = QUOTENAME(STREETADDRESS_e,'''')

or if you want double-quotes

update #temp2 set STREETADDRESS_e = QUOTENAME(STREETADDRESS_e,'"')
jpw
  • 44,361
  • 6
  • 66
  • 86