Skip to main content

Remove special character from string in MongoDB

Problem: Suppose we have a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.

Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.

Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.


{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}

{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}

{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}


Did you notice Information fields contains some special character so we need to remove these special character.

Query to remove special character from string in mongodb


db.getCollection('EduSurvey').aggregate([ { $project : { Name : 1 , About : 1,Information:1 } } ])
.forEach(function(doc,Index) {doc.Information=doc.Information.replace(/[^a-zA-Z 0-9 ]/g, ''); 
db.EduSurvey.update({ "_id": doc._id },{ "$set": { "Information": doc.Information } });});

db.getCollection('EduSurvey').aggregate([ { $project : { Name : 1 , About : 1,Information:1 } } ])


Result:



/* 1 */
{
    "_id" : ObjectId("579f33954237507d19a1897e"),
    "Name" : "JB institute",
    "About" : "This is good one collage for MBA",
    "Information" : "This institute has good faculty etc"
}

/* 2 */
{
    "_id" : ObjectId("579f33954237507d19a1897f"),
    "Name" : "MK institute",
    "About" : "This is good one collage for MCA",
    "Information" : "This is the dummy text12"
}

/* 3 */
{
    "_id" : ObjectId("579f33954237507d19a18980"),
    "Name" : "MG institute",
    "About" : "This is good one collage for B,Tech",
    "Information" : "This institute has good infrastructure"
}

Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str