remove double quotes from data dynamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

remove double quotes from data dynamically

Author

Topic

JHCOLVI
Starting Member

13 Posts

Posted - 2005-06-09 : 13:06:51

I need to remote double quotes surrounding the data of mulitple tables, with different column names. I have an sql query that does this if you know what the column names are. Here is a sample code I what to change:

INSERT TEMP1 (column1,column2,column3)
SELECT CASE WHEN SUBSTRING(column1,1,1) = '"' THEN SUBSTRING(column1,2,DATALENGTH(column1)-1)
ELSE column1
END,
SELECT CASE WHEN SUBSTRING(column2,1,1) = '"' THEN SUBSTRING(column2,2,DATALENGTH(column2)-1)
ELSE column2
END,
SELECT CASE WHEN SUBSTRING(column3,1,1) = '"' THEN SUBSTRING(column3,2,DATALENGTH(column3)-1)
ELSE column3
END
FROM TEMP

As you can see not all the data in the columns have double quotes around them. Also I will have a lot more than 3 columns. The problem is I will be picking the tables randomly and I have alot of tables to pick from, but I will only edit 1 table at a time. Is the there a way to get the column names dynamically and edit them like above?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-09 : 14:07:53

You will be picking the tables randomly? Why? To get the column names, you can use the INFORMATION_SCHEMA.COLUMNS view.

SELECT ...
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '...'

So get the tables, you can use INFORMATION_SCHEMA.TABLES.

Tara

JHCOLVI
Starting Member

13 Posts

Posted - 2005-06-09 : 16:46:53

I have to import CSV files into the SQL database whenever the file is updated on the server. It one of many files, but it is not always the same file. I do a bulk insert to get file into a table, but some columns of data are surrounded by double quotes.
The Information Schema will give me the list column names, how do I edit each column?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-09 : 16:53:01

You use the REPLACE function to get rid of double quotes in the data.

DECLARE @String varchar(50)

SET @String = '"SomeValueSurroundedByDoubleQuotes"'

SELECT @String AS NoChanges
SELECT REPLACE(@String , '"', '') AS RemovedDoubleQuotes

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-09 : 17:08:17

To do this dynamically, try this out:



SET NOCOUNT ON

CREATE TABLE Table1(Column1 varchar(50), Column2 varchar(50))

INSERT INTO Table1(Column1, Column2)
SELECT '"SomeValue1"', '"SomeValue2"' UNION ALL
SELECT '"SomeValue3"', '"SomeValue4"' UNION ALL
SELECT '"SomeValue5"', '"SomeValue6"'

SELECT * FROM Table1

DECLARE @i int, @SQL varchar(4000), @TableName sysname, @ColumnName sysname, @rc int
SET @i = 1

SELECT ORDINAL_POSITION, TABLE_NAME, COLUMN_NAME
INTO #Temp
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table1'

SELECT @TableName = TABLE_NAME, @ColumnName = COLUMN_NAME
FROM #Temp
WHERE ORDINAL_POSITION = @i

SET @rc = @@ROWCOUNT

WHILE @rc <> 0
BEGIN

	SET @SQL = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''"'', '''')'

	EXEC (@SQL)

	SET @i = @i + 1

	SELECT @TableName = TABLE_NAME, @ColumnName = COLUMN_NAME
	FROM #Temp
	WHERE ORDINAL_POSITION = @i

	SET @rc = @@ROWCOUNT

END

SELECT * FROM Table1

DROP TABLE #Temp, Table1

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-09 : 17:09:15

IMO, it would be easier to just edit out the double quotes from the file before it is imported into SQL Server or use DTS (which can strip these off for you on import).

Tara

JHCOLVI
Starting Member

13 Posts

Posted - 2005-06-09 : 19:42:00

Works great, Thank you Tara
I will also look into importing using DTS, but the importing is done from Visual Basic and I will have to check that out.

ankuch
Starting Member

1 Post

Posted - 2007-07-30 : 07:00:19

Hi Tara,

I do have problem with doble qoutes. .csv file. i am using this as sorce file in SQL 2005 package.
I am getting the error as "column delimter could not found".
The problem is with double qoutes, where some of the string appears like, ""ghdfghs"".
Can you please help to remove these double qoutes.

Anand

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 07:12:46

Open the file in a text editor and do a replace for two double quotes to one double quote

"" -> "

E 12°55'05.25"
N 56°04'39.16"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-07-30 : 23:25:13

If you want some speed... use Bulk Insert with a format file. \" is the escape character for quotes as a delimiter in BCP format files.

--Jeff Moden

shelhaslip
Starting Member

2 Posts

Posted - 2011-01-26 : 18:24:12

I know we can't have everything we want :-) ButI'm searching desperately for the solution to this exact same problem. I really want to use Bulk Insert, and I've created a proc to dynamically create the Format File (based on the headers on the associated Tab-Delimited file, and the column names in the table to be loaded). But we get data from multiple customers for these tables, and sometimes SOME of the fields have double-quotes. I'd LIKE a way to have Bulk Insert just ignore them (the way the OPTIONALLY ENCLOSED BY clause works in Oracle). Adding the quotes as part of the delimiter in the Format File doesn't work for me because the quotes are not necessarily always there. Also - we were trying to eliminate as much manual intervention as possible - so we were HOPING not to have to open the file in a text editor and do a Find & Replace before loading. I was even looking for a shell command that would remove all the double-quotes, that I could call from within the stored proc (I'm new to SQL Server and I think big). ANY ideas??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources