Wednesday, April 09, 2014

Match value in all the column of table

 

Dear All,

Recently one of colleague shared a new way of matching the value in all the columns.

Lets understand this by an example

We have person table in which  three column FirstName, LastName, MiddleName exists now suppose we need to find  in all the three column if any column contain letter “A”.

simply this query can be write as mention below

SELECT DISTINCT BusinessEntityID, FirstName,LastName,MiddleName FROM [Person].[Person] WHERE FirstName like ('%a%') Or LastName like ('%a%') Or MiddleName like ('%a%')  ORDER BY BusinessEntityID

Now same query can be write in following manner

SELECT DISTINCT BusinessEntityID, FirstName,LastName,MiddleName FROM [Person].[Person] WHERE FirstName + LastName + ISNULL(MiddleName,'') like ('%a%')  ORDER BY BusinessEntityID

So if you see above query we have concatenate  all the column and then apply like.

The important point is to remember here if a column contain NULL value then you have to convert it.

I hope this is another new way which I learnt hope it may be new for you.

So Enjoy,

Your host

Rajat Jaiswal

Monday, March 31, 2014

Build 2014–2nd–4th April

Dear all,

Its back enjoy build on 2nd & 4th April. Although I am very interested to join and see this great event live at San Francisco but unfortunately Sad smile not able to go there.

I will enjoy video

You can find more detail with below link

http://www.buildwindows.com/

So enjoy learning

Regards,

Rajat Jaiswal