How to write SQL Query to remove a given set of special characters from a selected column? [duplicate]
up vote
-2
down vote
favorite
This question already has an answer here:
How do you strip a character out of a column in SQL Server?
4 answers
I have a table [ABCTable]
When I query with
SELECT [XYZ] from [ABCTable]
there is a possibility that [XYZ] set of rows returned might contain - [~], [!], [@], [#], [$], [%], [^], [&], [*], [,], [.] , [?].
Is there a way to write just a SQL Query (not stored procedure or sub routines) to ensure these characters are removed while selecting the needed data ?
sql
sql-server tsql 
sql-server-2017 asked Nov 19 at 20:13
GilliVilla
1,57484275
marked as duplicate by Tab Alleman sql-server
Users with the sql-server badge can single-handedly close 
sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 at 20:23
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
|
show 6 more comments
up vote
-2
down vote
favorite
This question already has an answer here:
How do you strip a character out of a column in SQL Server?
4 answers
I have a table [ABCTable]
When I query with
SELECT [XYZ] from [ABCTable]
there is a possibility that [XYZ] set of rows returned might contain - [~], [!], [@], [#], [$], [%], [^], [&], [*], [,], [.] , [?].
Is there a way to write just a SQL Query (not stored procedure or sub routines) to ensure these characters are removed while selecting the needed data ?
sql
sql-server tsql sql-server-2017 asked Nov 19 at 20:13
GilliVilla
1,57484275
marked as duplicate by Tab Alleman sql-server
Users with the sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 at 20:23
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Create Function that will do it for you.
– slon
Nov 19 at 20:14
2
any reason you can't just useREPLACEfor each character?
– jtate
Nov 19 at 20:16
In SQL Server this is usually done using lots of nestedreplacebecause it's lackingtranslateandRegExp_replace:-(
– dnoeth
Nov 19 at 20:19
1
Not quite true @dnoeth TRANSLATE (SQL Server).
– Larnu
Nov 19 at 20:41
@Larnu: Great, finally available in SQL Server 2017, but why did MS implement it differently, so it can't be used to remove characters (without knowing a character which doesn't exist in the column and adding a finalreplace)?
– dnoeth
Nov 19 at 21:00
|
show 6 more comments
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
This question already has an answer here:
How do you strip a character out of a column in SQL Server?
4 answers
I have a table [ABCTable]
When I query with
SELECT [XYZ] from [ABCTable]
there is a possibility that [XYZ] set of rows returned might contain - [~], [!], [@], [#], [$], [%], [^], [&], [*], [,], [.] , [?].
Is there a way to write just a SQL Query (not stored procedure or sub routines) to ensure these characters are removed while selecting the needed data ?
sql
sql-server tsql sql-server-2017 asked Nov 19 at 20:13
GilliVilla
1,57484275
This question already has an answer here:
How do you strip a character out of a column in SQL Server?
4 answers
I have a table [ABCTable]
When I query with
SELECT [XYZ] from [ABCTable]
there is a possibility that [XYZ] set of rows returned might contain - [~], [!], [@], [#], [$], [%], [^], [&], [*], [,], [.] , [?].
Is there a way to write just a SQL Query (not stored procedure or sub routines) to ensure these characters are removed while selecting the needed data ?
This question already has an answer here:
How do you strip a character out of a column in SQL Server?
4 answers
sql
sql-server tsql sql-server-2017 sql
sql-server tsql sql-server-2017 asked Nov 19 at 20:13
GilliVilla
1,57484275
asked Nov 19 at 20:13
GilliVilla
1,57484275
asked Nov 19 at 20:13
GilliVilla
1,57484275
asked Nov 19 at 20:13
GilliVilla
1,57484275
asked Nov 19 at 20:13
GilliVilla
1,57484275
1,57484275
marked as duplicate by Tab Alleman sql-server
Users with the sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 at 20:23
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Tab Alleman sql-server
Users with the sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 at 20:23
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Create Function that will do it for you.
– slon
Nov 19 at 20:14
2
any reason you can't just useREPLACEfor each character?
– jtate
Nov 19 at 20:16
In SQL Server this is usually done using lots of nestedreplacebecause it's lackingtranslateandRegExp_replace:-(
– dnoeth
Nov 19 at 20:19
1
Not quite true @dnoeth TRANSLATE (SQL Server).
– Larnu
Nov 19 at 20:41
@Larnu: Great, finally available in SQL Server 2017, but why did MS implement it differently, so it can't be used to remove characters (without knowing a character which doesn't exist in the column and adding a finalreplace)?
– dnoeth
Nov 19 at 21:00
|
show 6 more comments
Create Function that will do it for you.
– slon
Nov 19 at 20:14
2
any reason you can't just useREPLACEfor each character?
– jtate
Nov 19 at 20:16
In SQL Server this is usually done using lots of nestedreplacebecause it's lackingtranslateandRegExp_replace:-(
– dnoeth
Nov 19 at 20:19
1
Not quite true @dnoeth TRANSLATE (SQL Server).
– Larnu
Nov 19 at 20:41
@Larnu: Great, finally available in SQL Server 2017, but why did MS implement it differently, so it can't be used to remove characters (without knowing a character which doesn't exist in the column and adding a finalreplace)?
– dnoeth
Nov 19 at 21:00
Create Function that will do it for you.
– slon
Nov 19 at 20:14
Create Function that will do it for you.
– slon
Nov 19 at 20:14
2
2
any reason you can't just use
REPLACE for each character?– jtate
Nov 19 at 20:16
any reason you can't just use
REPLACE for each character?– jtate
Nov 19 at 20:16
In SQL Server this is usually done using lots of nested
replace because it's lacking translate and RegExp_replace :-(– dnoeth
Nov 19 at 20:19
In SQL Server this is usually done using lots of nested
replace because it's lacking translate and RegExp_replace :-(– dnoeth
Nov 19 at 20:19
1
1
Not quite true @dnoeth TRANSLATE (SQL Server).
– Larnu
Nov 19 at 20:41
Not quite true @dnoeth TRANSLATE (SQL Server).
– Larnu
Nov 19 at 20:41
@Larnu: Great, finally available in SQL Server 2017, but why did MS implement it differently, so it can't be used to remove characters (without knowing a character which doesn't exist in the column and adding a final
replace)?– dnoeth
Nov 19 at 21:00
@Larnu: Great, finally available in SQL Server 2017, but why did MS implement it differently, so it can't be used to remove characters (without knowing a character which doesn't exist in the column and adding a final
replace)?– dnoeth
Nov 19 at 21:00
|
show 6 more comments
1 Answer
1
active
oldest
votes
up vote
3
down vote
Do you want something like
CREATE TABLE T(
ID INT IDENTITY(1,1),
Value VARCHAR(45)
);
INSERT INTO T(Value) VALUES
('.A*B$C@'),
('D#E$,F'),
('.G,H*I@$');
DECLARE @Chars VARCHAR(45) = '@$.,*#';
SELECT *, REPLACE(TRANSLATE(Value, @Chars, REPLICATE(' ', LEN(@Chars))), ' ', '') Result
FROM T;
Returns:
+----+----------+--------+
| ID | Value | Result |
+----+----------+--------+
| 1 | .A*B$C@ | ABC |
| 2 | D#E$,F | DEF |
| 3 | .G,H*I@$ | GHI |
+----+----------+--------+
Demo
Note: If you have WhiteSpaces there I suggest that you use CHAR(9) instead as
REPLACE(TRANSLATE(Value, @Chars, REPLICATE(CHAR(9), LEN(@Chars))), CHAR(9), '')
answered Nov 19 at 20:56
Sami
6,65531038
Exactly as I wrote in my comment, you need to nest it in a Replace, hopefully there's no space within the data...
– dnoeth
Nov 19 at 21:47
This solution works for the sample data you provided but I would not use a space as the dummy REPLACE value. Obviously values that have spaces will get messed up. I'd go with CHAR(1) or another character that is less common.
– Alan Burstein
Nov 19 at 23:17
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
Do you want something like
CREATE TABLE T(
ID INT IDENTITY(1,1),
Value VARCHAR(45)
);
INSERT INTO T(Value) VALUES
('.A*B$C@'),
('D#E$,F'),
('.G,H*I@$');
DECLARE @Chars VARCHAR(45) = '@$.,*#';
SELECT *, REPLACE(TRANSLATE(Value, @Chars, REPLICATE(' ', LEN(@Chars))), ' ', '') Result
FROM T;
Returns:
+----+----------+--------+
| ID | Value | Result |
+----+----------+--------+
| 1 | .A*B$C@ | ABC |
| 2 | D#E$,F | DEF |
| 3 | .G,H*I@$ | GHI |
+----+----------+--------+
Demo
Note: If you have WhiteSpaces there I suggest that you use CHAR(9) instead as
REPLACE(TRANSLATE(Value, @Chars, REPLICATE(CHAR(9), LEN(@Chars))), CHAR(9), '')
answered Nov 19 at 20:56
Sami
6,65531038
Exactly as I wrote in my comment, you need to nest it in a Replace, hopefully there's no space within the data...
– dnoeth
Nov 19 at 21:47
This solution works for the sample data you provided but I would not use a space as the dummy REPLACE value. Obviously values that have spaces will get messed up. I'd go with CHAR(1) or another character that is less common.
– Alan Burstein
Nov 19 at 23:17
add a comment |
up vote
3
down vote
Do you want something like
CREATE TABLE T(
ID INT IDENTITY(1,1),
Value VARCHAR(45)
);
INSERT INTO T(Value) VALUES
('.A*B$C@'),
('D#E$,F'),
('.G,H*I@$');
DECLARE @Chars VARCHAR(45) = '@$.,*#';
SELECT *, REPLACE(TRANSLATE(Value, @Chars, REPLICATE(' ', LEN(@Chars))), ' ', '') Result
FROM T;
Returns:
+----+----------+--------+
| ID | Value | Result |
+----+----------+--------+
| 1 | .A*B$C@ | ABC |
| 2 | D#E$,F | DEF |
| 3 | .G,H*I@$ | GHI |
+----+----------+--------+
Demo
Note: If you have WhiteSpaces there I suggest that you use CHAR(9) instead as
REPLACE(TRANSLATE(Value, @Chars, REPLICATE(CHAR(9), LEN(@Chars))), CHAR(9), '')
answered Nov 19 at 20:56
Sami
6,65531038
Exactly as I wrote in my comment, you need to nest it in a Replace, hopefully there's no space within the data...
– dnoeth
Nov 19 at 21:47
This solution works for the sample data you provided but I would not use a space as the dummy REPLACE value. Obviously values that have spaces will get messed up. I'd go with CHAR(1) or another character that is less common.
– Alan Burstein
Nov 19 at 23:17
add a comment |
up vote
3
down vote
up vote
3
down vote
Do you want something like
CREATE TABLE T(
ID INT IDENTITY(1,1),
Value VARCHAR(45)
);
INSERT INTO T(Value) VALUES
('.A*B$C@'),
('D#E$,F'),
('.G,H*I@$');
DECLARE @Chars VARCHAR(45) = '@$.,*#';
SELECT *, REPLACE(TRANSLATE(Value, @Chars, REPLICATE(' ', LEN(@Chars))), ' ', '') Result
FROM T;
Returns:
+----+----------+--------+
| ID | Value | Result |
+----+----------+--------+
| 1 | .A*B$C@ | ABC |
| 2 | D#E$,F | DEF |
| 3 | .G,H*I@$ | GHI |
+----+----------+--------+
Demo
Note: If you have WhiteSpaces there I suggest that you use CHAR(9) instead as
REPLACE(TRANSLATE(Value, @Chars, REPLICATE(CHAR(9), LEN(@Chars))), CHAR(9), '')
answered Nov 19 at 20:56
Sami
6,65531038
Do you want something like
CREATE TABLE T(
ID INT IDENTITY(1,1),
Value VARCHAR(45)
);
INSERT INTO T(Value) VALUES
('.A*B$C@'),
('D#E$,F'),
('.G,H*I@$');
DECLARE @Chars VARCHAR(45) = '@$.,*#';
SELECT *, REPLACE(TRANSLATE(Value, @Chars, REPLICATE(' ', LEN(@Chars))), ' ', '') Result
FROM T;
Returns:
+----+----------+--------+
| ID | Value | Result |
+----+----------+--------+
| 1 | .A*B$C@ | ABC |
| 2 | D#E$,F | DEF |
| 3 | .G,H*I@$ | GHI |
+----+----------+--------+
Demo
Note: If you have WhiteSpaces there I suggest that you use CHAR(9) instead as
REPLACE(TRANSLATE(Value, @Chars, REPLICATE(CHAR(9), LEN(@Chars))), CHAR(9), '')
answered Nov 19 at 20:56
Sami
6,65531038
edited Nov 19 at 23:37
answered Nov 19 at 20:56
Sami
6,65531038
answered Nov 19 at 20:56
Sami
6,65531038
answered Nov 19 at 20:56
Sami
6,65531038
6,65531038
Exactly as I wrote in my comment, you need to nest it in a Replace, hopefully there's no space within the data...
– dnoeth
Nov 19 at 21:47
This solution works for the sample data you provided but I would not use a space as the dummy REPLACE value. Obviously values that have spaces will get messed up. I'd go with CHAR(1) or another character that is less common.
– Alan Burstein
Nov 19 at 23:17
add a comment |
Exactly as I wrote in my comment, you need to nest it in a Replace, hopefully there's no space within the data...
– dnoeth
Nov 19 at 21:47
This solution works for the sample data you provided but I would not use a space as the dummy REPLACE value. Obviously values that have spaces will get messed up. I'd go with CHAR(1) or another character that is less common.
– Alan Burstein
Nov 19 at 23:17
Exactly as I wrote in my comment, you need to nest it in a Replace, hopefully there's no space within the data...
– dnoeth
Nov 19 at 21:47
Exactly as I wrote in my comment, you need to nest it in a Replace, hopefully there's no space within the data...
– dnoeth
Nov 19 at 21:47
This solution works for the sample data you provided but I would not use a space as the dummy REPLACE value. Obviously values that have spaces will get messed up. I'd go with CHAR(1) or another character that is less common.
– Alan Burstein
Nov 19 at 23:17
This solution works for the sample data you provided but I would not use a space as the dummy REPLACE value. Obviously values that have spaces will get messed up. I'd go with CHAR(1) or another character that is less common.
– Alan Burstein
Nov 19 at 23:17
add a comment |
Create Function that will do it for you.
– slon
Nov 19 at 20:14
2
any reason you can't just use
REPLACEfor each character?– jtate
Nov 19 at 20:16
In SQL Server this is usually done using lots of nested
replacebecause it's lackingtranslateandRegExp_replace:-(– dnoeth
Nov 19 at 20:19
1
Not quite true @dnoeth TRANSLATE (SQL Server).
– Larnu
Nov 19 at 20:41
@Larnu: Great, finally available in SQL Server 2017, but why did MS implement it differently, so it can't be used to remove characters (without knowing a character which doesn't exist in the column and adding a final
replace)?– dnoeth
Nov 19 at 21:00