How to remove Special Character in database output

A few months back i was caught in situation where we had special characters in data base like shift+enter, tab , space , enter etc.

That time we spend 3 hours to detect issue and next 3 hours to resolve it. But a few days back one of my colleague get caught in same situation we helped her to detected the issue and suggested the same solution we put at that time, but somehow she managed to get more easy approach to resolve the issue.

so if a string has special characters in it, then it is better approach to change value to string and than perform a trim on the charcter we want to remove.

for eg:

string s= dr.GetString("column1");

assuming s contains a tab and enter character in it.

s=s.ToString().Trim("\t"); // it will remove tab character form string
s=s.ToString().Trim("\n"); // it will remove enter character from string

so it is better to use Trim instead of performing looping on character and using ASCII to remove character.

Note: But remember, you need to use ToString method before using Trim method. If we wont do that there wont be any affect on string.

Happy Living....
Happy Concepts....
Happy Programming....

Allow Null values In Inner join

Recently i got stuck in a scenario where i had to allow nulls in inner join and also sequece needed to be the same as inner join.

For eg:
We have a table with following schema

PersonDetails:
       -----------------------------------------------------
       Id         Name            ProjectId           TechGroupId
       ------------------------------------------------------
       1           m1                  null                   30
       2            m2                  p1                     null 


Projects :
      -------------------------------------------------------
       Id                            Name
      -------------------------------------------------------
       p1                           Project1
       p2                           Project2   

TechGroups :
      -------------------------------------------------------
       Id                            Name
      -------------------------------------------------------
       1                           dotnet
       2                           java   
       30                         Salesforce 

And user expecting following result:

     Id         Name            Project                  TechGroup
       --------------------------------------------------------------
       1           m1                  null                    SalesForce
       2            m2                  project1                     null 

Solution:

we can create to temptables as following
Select * into #Projects from Projects
select * into #TechGroups from TechGroups

Now add nulls in temp tables.

Insert #Projects
values (null , null)

Insert #TechGroups
Values(null,null)


and To get expected result query should be:

Select * from PersonalDetails pd
inner join #Projects p on  (pd.Projectid is null and p.Id is null ) or pd.Projectid=p.Id
inner join #TechGroups tg on  (pd.TechGroupid is null and tg.Id is null ) or pd.TechGroupid=tg.Id