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

“dynamic” new feature in .NET 4.0 , How to use it ?

Dear Friends,
VS 2010 introduce a new variable type which is dynamic.
By the name it is clear that it will have dynamic property means what will be the data type will be decide at run time.
Suppose if I declare dynamic Name = “rajat”

Then Name variable considered as a string variable

Simillarly dynamic Age = 21

Then Age variable is considered as integer.

To understand it more lets follow the example below.

I have declared a test variable which is dynamic type and on each line I assign a different value to the variable as shown in below fig.

dynamic Variable Example

When you run this set of statements you will find following result.

dynamic Variable Type Result

The first variable assignment is considered as integer, the second variable assignment is considered as a string and after last value which is date time variable considered as datatime variable.

So we have seen at each assignment of the value the nature of variable is also changed which is so dynamic that’s why the name it is called dynamic variable type. It Means it bypass compiler time checking.

I hope it give some hit how to use dynamic and where we can use it.

Thanks & best regards,
Rajat Jaiswal