Menu
I have a table in a SQL Server database with an NTEXT column. This column may contain data that is enclosed with double quotes. When I query for this column, I want to remove these leading and trailing quotes.
For example:
"this is a test message"
should become
this is a test message
I know of the LTRIM and RTRIM functions but these workl only for spaces. Any suggestions on which functions I can use to achieve this.
985
SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.
As cormaco says it's doubtful you have hidden quotes in the table data. REPLACE(item_description, '"', '') will absolutely remove any " from the column.
Basically it performs three steps: Replace each 0 with a space – REPLACE([CustomerKey], '0', ' ') Use the LTRIM string function to trim leading spaces – LTRIM(<Step #1 Result>) Lastly, replace all spaces back to 0 – REPLACE(<Step #2 Result>, ' ', '0')
I have just tested this code in MS SQL 2008 and validated it.
Remove left-most quote:
UPDATE MyTable SET FieldName = SUBSTRING(FieldName, 2, LEN(FieldName)) WHERE LEFT(FieldName, 1) = '"' Remove right-most quote: (Revised to avoid error from implicit type conversion to int)
UPDATE MyTable SET FieldName = SUBSTRING(FieldName, 1, LEN(FieldName)-1) WHERE RIGHT(FieldName, 1) = '"' I thought this is a simpler script if you want to remove all quotes
UPDATE Table_Name SET col_name = REPLACE(col_name, '"', '') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
