DarkHorse65
asked on
Different String delimitator in a TSQL sentence
Consider the following situation:
1- An application reading a text file.
2- Maybe some of the records contain Single or Double quotes
3- The application must build a TSQL sentence to insert the data into the database
4- The data quotes could be at the beginning and/or the end of a field or perhaps in the middle.
5- How many adjacent quotes is not known
The problem:
When enveloping the data field into single or double quotes and running it against SQL Server 2005 it raises an exception when there are quotes already into it.
Example:
Data: The Vendor's device is blue
SQL: INSERT INTO dbo.MyTable SELECT 'The Vendor's device is blue' -- This raises an error
Is there a way to change the strings delimitator to use for example pipes instead of quotes?
SQL: INSERT INTO dbo.MyTable SELECT |The Vendor's device is blue|
1- An application reading a text file.
2- Maybe some of the records contain Single or Double quotes
3- The application must build a TSQL sentence to insert the data into the database
4- The data quotes could be at the beginning and/or the end of a field or perhaps in the middle.
5- How many adjacent quotes is not known
The problem:
When enveloping the data field into single or double quotes and running it against SQL Server 2005 it raises an exception when there are quotes already into it.
Example:
Data: The Vendor's device is blue
SQL: INSERT INTO dbo.MyTable SELECT 'The Vendor's device is blue' -- This raises an error
Is there a way to change the strings delimitator to use for example pipes instead of quotes?
SQL: INSERT INTO dbo.MyTable SELECT |The Vendor's device is blue|
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The application was a sort of a custom DTS except that files reside in User's computer. There is some pre-processing involved according with dynamic parameters. That's why quotes processing was mandatory.
Thank You.