How to replace few characters in SQL Server and node js

Question

I need to replace following characters in two places (nodejs app and SQL Server database). Characters are: - ( , . ) [ ] _

String can be in any language like English, Japanese etc.

Node Js and SQL server both has replace in-built function and I am using that. These inbuild function is not doing the work.

In Node js when I read data from database using package "node-mssql" it is getting converted to question mark and replace is not working.

In SQL Server, the replace function is treating drawing line as dash and replacing it with empty string.

What is the right way to do it in both SQL Server and Node Js.

SQL Server version is 2016

Your question is not clear? You want a common replace function in both SQL Server and NodeJS? If you don't want a common method then it needs to be 2 questions. And you need to provide some example data to illustrate what you are trying to accomplish and why its not working. — Dale K, Aug 19 '19 at 20:16
You have multiple issues here and it will serve you better to separate them into different questions. First, you have an application problem that allows characters to get into the database that should not be allowed. Next, you need to fix the existing data in your database. Next, you have a problem retrieving strings in your app and not seeing exactly what is in the database once retrieved. Lastly you have an application problem about removing undesirable characters from a string. All of those are part of your issue but you can't address all of them at once. Divide and conquer. — SMor, Aug 19 '19 at 20:29

Lukasz Szozda · Answer 1 · 2019-08-19T20:34:51.430

0

SQL Server 2017+ TRANSLATE:

SELECT TRANSLATE(column, N'-(,.)[]_', N'        ')
FROM tab

For SQL Server you could use REPLACE function.

In SQL Server, the replace function is treating drawing line as dash and replacing it with empty string.

You should mark literals with N (NVARCHAR):

SELECT REPLACE(N'━', N'-', N'');
-- ━

SELECT REPLACE('━', '-', '')
-- ?

edited Aug 19 '19 at 20:34

answered Aug 19 '19 at 20:11

Lukasz Szozda

I am using SQL server 2016 – Anil Aug 19 '19 at 20:13
Then you need to use nested replace – Lukasz Szozda Aug 19 '19 at 20:14
In SQL Server, the replace function is treating drawing line as dash and replacing it with empty string. – Anil Aug 19 '19 at 20:26
@Anil `is treating drawing line as dash` I doubt it. They are different characters. You should check data type and collation. – Lukasz Szozda Aug 19 '19 at 20:36

1 Answers1