How to remove unicode/symbol/invalid charcters exist in the first_name

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I have tried to create a function to handle the unicode/unwanted/invalid character in the table but i am getting some issue there because in my table have also other languages/symbol/invalid exits and data like Chinese,Spanish,Russian,symbol also exits etc.

When i am using this function then other languages/symbol converted into ????

please help me here for the issue.

Basically i need this to clean first name and last name before to share data with clients.

First Name result
♥๘ إ ζـسآآس ?????????
ندا ???
محمد حسن ???????
JEAN-LUC JEAN-LUC
I's Is
MM-almoneef MM-almoneef
برآءه ?????
الحر ????
غلا ???
وديان ?????

What I have tried:

CREATE FUNCTION dbo.UDF_Removecharacter
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z一个-同-]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z个-同-]%', @string)
END
SET @string = @string
RETURN @string
END

Posted 23-Apr-17 0:00am

asyadav

Updated 23-Apr-17 2:35am

Add a Solution

Comments

[no name] 23-Apr-17 7:23am

Not clear, at least not for me. Filenames and Unicode/symbols are not related directly. From my Point of view there is only a set of charactess which are _not_ allowed in a filename. Have a look here:
Naming Files, Paths, and Namespaces (Windows)[^]

asyadav 24-Apr-17 3:05am

Hi thanks for the comments.
Actually i am not a developer. i am working as a data analyst so as per my understanding i have created this.

My question is here that i need to remove some symbols which is exists in first_name column of table.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2017-04-23T02:35:00

Solution 1

It seems you not actually using Unicode in SQL... [^]
Use NVARCHAR instead of VARCHAR and prefix string constants with N''...

Posted 23-Apr-17 2:35am

Kornfeld Eliyahu Peter

Comments

asyadav 24-Apr-17 3:22am

I have changed the code as you suggested but still i am getting error, could you please help me here actually i am not a developer, i am working as a data analyst so always i do manually in excel file it takes too much time because data count is high.

Alter FUNCTION dbo.UDF_Removecharacter
(
@string NVARCHAR(1000)
)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX(N'%[^0-9A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, ' ')
SET @IncorrectCharLoc = PATINDEX(N'%[^0-9A-Za-z]%', @string)
END
SET @string = @string
RETURN @string
END

Getting error
Msg 0, Level 11, State 0, Line 31
A severe error occurred on the current command. The results, if any, should be discarded.

Kornfeld Eliyahu Peter 24-Apr-17 3:37am

The error here has nothing to do with Unicode or such... You need to isolate the line (as no line 31 in your function) on which it fails... However, a lot of times such error is about problem with data integrity, like corrupt indexes...

asyadav 24-Apr-17 3:42am

7 lacks data in the table where i am using below select statement :

select [Ind First Name],dbo.UDF_Removecharacter([Ind First Name]) from Table_Name

asyadav 24-Apr-17 3:45am

Below mention type of junk charter exists in column value which i need to remove

███▓▒░░▀▄▀▄▀▄oO ★ＴＥＮＺＯ★ Oo▀▄▀▄▀░░▒▓███

§©ö®pïöñ

☺MØÜÐÝ☺

ْ •●ҳ̸ҳÙsêf

♥ṠảЯờήāђ

......

----