SQL Query | Replace special characters | Control Characters | REPLACE function

Sharing buttons:

hello everybody and welcome to another

tutorial with learn no star

in today's sequel session we are going

to take a look at a scenario based sql

query which shows how to replace special


from a string so let's get started

we are going to work with the data from

the employee table and this is how the

data looks like

we are mainly concerned here with the

address column

so the first attempt that we are trying

to make is to simply clean the data from

the address column

and trim any leading or trailing spaces

that we might have in this column

so let's go ahead and write this query

now obviously the first function that

comes to your mind is going to be the

trim function which is used for this

purpose of removing any leading or

trailing spaces

so let's go ahead and write this query

let's select all the columns from this


and then just trim on the address column

using the trim function and

the expected result is

that all the records will have the

leading and trailing spaces trimmed

but if you take a closer look at the

data and go to

the data for employee id 5 we see that

there was a leading space here on 10

main street

and there's still a space remaining over


in spite of using the trim function if

you just copy this data

from here and maybe go

and paste it to a

notepad you'll see that there is still

there is at least some character over

here now if it is not a space character

then it is some kind of a control

character which uh we are not able to


through our eyes but there has to be

some character

special character that needs to be

removed now what can you do

in these cases so for scenarios like

this we have to make use of another


which is called the replace function now

replace function

as the name suggests simply replaces

some string or some

pattern from the string with whatever

new expression that you specify

if you just write the replace function

it will give you a prompt telling you

how the format should be so the first

parameter argument to be passed is the

expression to be searched

then your search expression and the

replacement expression

now the expression to be searched for us

is the address column

so we have to put that and we are trying


look for these special characters

replace function

works as well with non-special

characters so let's say there's a

requirement to

remove the spaces not just the leading

and trailing spaces but also any spaces

that occur in between this ring

then we can make use of the replace

function so we have specified the

expression in which we want to replace

the spaces

and then we have to specify

what we want to replace so that is going

to be a space within single quotes

and we want to replace it with empty

let's say

and just close the bracket and let's run

this query and let's see what is the

output in this case

so if you execute this query you will

see that all the spaces have been


and still we can see some records have

something which looks like a space to

the plane i

but these are obviously special

characters that still need to be removed

so we are going to make use of the same

function now

to be able to remove the special

characters we need to use the

s key representation of those special


so we need to know what are the

equivalent ascii

codes for these special characters now

most of the time the special characters

are of the

type tab the line feed or the carriage


these special characters can occur in

the data whenever the data is coming

directly from

the source or the user and it's mostly a

free flowing text like the address field

where people just type

in they sometimes press the backspace

carriage return and all these characters


caught in the user input so once we have

identified the corresponding

s key codes so what we are going to

consider here are mostly tap

characters the carriage return and the

line feed characters

and if you take a look the corresponding

quotes here are 9

10 and for carriage return it's going to

be 13.

so now let's make use of this replace

function to replace these special


so here we have to type the character

that we want to be replaced

using its ascii code now you cannot just

simply put the ascii code you have to

put the s key code within the car

function so you have to put cat and the

corresponding s key code

so for example let's say 9 so in this

case what we are going to do

is replace and the ascii code 9 and if

we go back to

our input we will see that the ascii


9 corresponds to tab with an

empty string so let's execute this query

once again and see

what is the output now if you pay close

attention you will be able to see again

this record for employee id 5

now there is no space

that is visible to the plane i that

means there is no special character

which is present in the string so that

was the this was the one that we

captured earlier using the trim function

and now let's go back and

let's paste this so now you can see that

there is no space

in the beginning of this string whereas

when you use the trim function there was

still a space so

there was a special character right over

here which has been replaced and that

special character was

tab because we used cal9 to replace that

special character

similarly we can just keep on nesting

this replays

so once we have replaced the

the tabs now what we want to do is

replace the result

of this and now we want to replace

the line feeds and the carriage return

so let's go with cat 13

and replace it again with empty

and run this spring this query

and now let's replace it

with any line feeds as well so replace

so you have to just keep on nesting it

and put the s key code within the car so


10 and replace it with again with an


and now run this query

and now you'll see that most of the

address columns have

been corrected and now you can also see

that in some of them there was actually

no space

but only special characters which looked

like space for example

this particular value so if i just paste

it over here

now it looks that there were some spaces

here but when


paste the value that we have got after

running our replace commands

you can see that there are

no spaces or no characters remaining

between park and street so there was it

was actually a single word

and all those special characters have

now been trimmed

so this is a very simple and easy method

to replace any special characters that

might occur in your strings in your


tables this is also an interview

question so i hope that you found this


and we'll be posting many more videos on

scenario sql queries they would be

coming very soon so make sure that you

subscribe to our youtube channel

thank you and have a good day