x

The replace function with nvarchar(max) strings

Hi Friends, I have an itemnumber field in my table i.e(114"CCALUM8'SHEETS) and the datatype is nvarchar(10) and it is having special charecters("",'). Can anyone give me the code to replace ("",') with empty space. I used replace function in sql as select replace('114"CCALUM8'SHEETS',''','') but i am getting error because uneven quotes in my itemnumber field. Please help me in this.

   Thanks.
more ▼

asked Aug 24 '12 at 10:55 AM in Default

sanjeev.matte gravatar image

sanjeev.matte
31 8 8 10

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Do you need this?

DECLARE @string NVARCHAR(MAX)
SELECT  @string = '114""CCALUM8''SHEETS'
SELECT  @string OriginalString, REPLACE(REPLACE(REPLACE(@string, '''', ''), '"', ''), ',', '') StringWithoutUnwantedChars
more ▼

answered Aug 24 '12 at 11:07 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thanks for the answer but here we are not using any param to store the itemnumber. we are directly fetching it from the table.
Aug 24 '12 at 12:02 PM sanjeev.matte
Just replace @String with the column name i guess.
Aug 24 '12 at 12:05 PM sp_lock
Yes..Its working..Thanks allot..
Aug 24 '12 at 12:08 PM sanjeev.matte
@sanjeev.matte Glad to know it helped. But can you please return us the favor by marking the answer as accepted? This will help the future users as well.
Aug 24 '12 at 12:14 PM Usman Butt
The answer is accepted..Thanks..
Aug 24 '12 at 12:44 PM sanjeev.matte
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x18

asked: Aug 24 '12 at 10:55 AM

Seen: 682 times

Last Updated: Aug 24 '12 at 01:01 PM