Link to home
Start Free TrialLog in
Avatar of DarkHorse65
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|
ASKER CERTIFIED SOLUTION
Avatar of fernandonajera
fernandonajera

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DarkHorse65
DarkHorse65

ASKER

Yes I worked for me.

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.