replace "ó" to "?" 2

[baharehresidegan]

hi
i have an excel file that i import it to sql server
this file has some records with this character: "ó"
and i cant replace it to "?" and other characters.
i must tell u that : i can do it manually but i want to do it with a Tsql Command.
tanx

 

[micang]

Hi,

Won't this do the trick?

UPDATE table
SET column_name = Replace(column_name,'ó','')

Hope this helps.

Michael

 

[bborissov]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]REPLACE[/color](YourField,[COLOR=red]'o'[/color],[COLOR=red]'?'[/color]) [COLOR=blue]FROM[/color] YourTable

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

Sorry, ignore previous post.

Below should do what you want.

UPDATE table
SET column_name = Replace(column_name,'ó','?')
where column_name = 'ó'

Hope this helps.

Michael

 

[bborissov]

Better be:

where column_name LIKE '%ó%'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

You're right Borislav!

 

[baharehresidegan]

Dear Friends
i know this way . it is not useful for my problem .
you can test this command and see that command doesn't solve my problem
.

 

[micang]

baharehresidegan,

We going to need more information then, can you post some sample data?

Michael

 

[baharehresidegan]

sample for u :
For example :
my table is "Table2"
"Table2" has One field named "a" (Type = nvarchar(50))
for example these records are in "table2"

ó456
fgró456
refg45ó567

I cant use "replace" command for change "ó" to "?"
this command doesn't solve my problem

 

[ca8msm]

I cant use "replace" command for change "ó" to "?"

Why can't you?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[baharehresidegan]

Dear ca8msm
If i knew my problem , i solve it.
but i dont konw
i used "replace" command in my programs many times
but i dont underestand why it not useful for me to replace "ó" to "?

 

[onpnt]

I'm confused.

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[baharehresidegan]

dear onpnt
why u r confused?
could u use "replace" command for my problem?

 

[onpnt]

I'm confused because there is no valid reasoning behind not being able to use the built in replace function. At least you haven't given one.

The example you have given only pushes one reply to your thread. That being use the Replace() function.

Unless there is some underlying reasoning behind why this function will not work for you I'm not sure there is a more efficient or stable method to do this task.

Is there one?

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[bborissov]

baharehresidegan,
Maybe you try to replace wrong char. Go to record where you have such char find the position and issue this:

SELECT ASCII(SUBSTRING(YourField,position of that char,1))
       FROM YourTable
       WHERE PK = (primary key value of the record where that char exists)

That could give you correct ASCII value of that char. The you could use:

UPDATE YourTable
       SET YourField = REPLACE(YourField,CHAR(ASCII code you got before), '?')

(for me ASCII value of that char is 111) but you should check it on your side.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

I'm stumped. If it's once off why not do the find replace in Excel maybe.

Michael

 

[onpnt]

Because Excel is a horribly unstable environment. You should never reply on it other than viewing purposes. It is a desktop application meant for the users.

That is of course unless you want to have your business fall to its knees in the event a simple office update decides your functions need to be slightly different due to security risks and all thus breaking everything you are doing.

Along with creating a maintenance situation that is never ending. Job security right? ;-)

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[micang]

Very true onpnt...what can I say, I'm probably just as unstable as Excel!

I find the above very weird. I imported an excel sheet containing ó in SQL 2000 and used the replace command no problem.

Michael

 

[ca8msm]

Dear ca8msm
If i knew my problem , i solve it.
but i dont konw
i used "replace" command in my programs many times
but i dont underestand why it not useful for me to replace "ó" to "?"

Your replies don't make any sense. The Replace command works perfectly, will work for you in this case and a code solution using this Replace command has been given to you (by both micanguk and bborissov). You are are the one who is saying you can't use it yet you don't give a reason why you can't use it. This is why everyone seems confused at your posts.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[baharehresidegan]

MY DEAR FRIENDS
TANX VERY MUCH