Len Vs Datalength in sql server

I have seen a very common question like, Whats the difference between len and datalength function in SQL Server? (Important Interview question too... )

I have created a table name 'VENKATTABLE'

CREATE TABLE VENKATTABLE(NAME VARCHAR(100))
INSERT INTO VENKATTABLE VALUES('VENKAT ')
SELECT * FROM VENKATTABLE

Now, I've tried to use these statements,

SELECT LEN(NAME) FROM VENKATTABLE -- OUTPUT =>6
SELECT DATALENGTH(NAME) FROM VENKATTABLE -- OUTPUT =>9

The difference is length will trim the data and it wont consider the spaces available. Whereas, Datalength will fetch the exact data available and it will consider the spaces too.

In this case, I've tried with Ltrim and Rtrim to achieve the same functionality of len function.

SELECT DATALENGTH(LTRIM(RTRIM(NAME))) FROM VENKATTABLE -- OUTPUT =>6

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Tamil Joke - If we got chance to speak truth in interview

வேலைக்கான நேர்காணலில் ... உண்மையைச் சொல்ல முடிந்தால் ....

நீங்கள் ஏன் எங்கள் நிறுவனத்தில் பணிபுரிய விரும்புகிறீர்கள் ..?

எந்தப் புண்ணாக்குக் கம்பெனியிலாவது வேலை செஞ்சாதான் பொழப்ப ஓட்ட முடியும்.. எந்த நாய் வேலை குடுக்குதோ அங்க வேலை செய்ய வேண்டியதுதான் .. அதைத் தவிர உன் கம்பேனி மேல பெருசா ஒண்ணும் மதிப்பு மரியாதையெல்லாம் இல்லே ..!

உங்களுக்கு ஏன் இந்த வேலையைத் தரவேண்டும் ..?
உன் கம்பெனி வேலையை யாராவது ஒருத்தன் செஞ்சுதானே ஆகணும்... என்கிட்டதான் கொடுத்துப் பாரேன்.

உங்களுடைய தனித்திறமை என்ன ..?

வேலைக்கு சேர்ந்ததும் , கடலை போட வழியிருக்கான்னு பார்ப்பேன்.. இங்கேருந்து என்னென்ன சுடலாம்ன்னு நோட்டம் உடுவேன் .. உன் கம்பெனியில் வேலைக்கு சேர்ந்ததைச் சொல்லி ஊர் பூரா கடன் வாங்குவேன்.. அப்புறம் வேற கம்பெனிக்கு தாவ முயற்சி பண்ணுவேன் .. இதைத் தவிர உன் கம்பெனிக்கு சேவை செஞ்சு முன்னுக்குக் கொண்டு வரணும்ங்கிற மூட நம்பிக்கையெல்லாம் கிடையாது .

உங்கள் மிகப்பெரிய பலம்..?
இதைவிட பெரிய சம்பளத்தில் வேலை கிடைச்சா அப்படியே உட்டுட்டு அங்கே ஓடிருவேன் .. மனசாட்சி, நன்றியுணர்வு இதுக்கெல்லாம் முட்டாள்தனமா ,, இடமே கொடுக்காம கடுமையா நடந்துக்குவேன் ..

பலவீனம்..?

ஹி.. ஹி... பெண்கள்..!

இதற்கு முன் வேலை பார்த்த நிறுவனத்தில் உங்கள் சாதனைகள் என்ன..?அப்படி ஏதும் இருந்தா நான் ஏன் வேலை தேடி இங்கே வருகிறேன் .. அந்த சாதனைகளை பெருசா பில்டப் பண்ணி அங்கேயே வேணும்ங்கிற அளவுக்கு சம்பளத்தைக் கறந்துருக்க மாட்டேனா ..?

நீங்கள் சந்தித்த மிகப்பெரும் சவால் என்ன ..?

அதை எப்படி வெற்றி கொண்டீர்கள் ..?ஆண்டவன் அருள்தான் காரணம்.. இதுவரைக்கும் எந்த நிர்வாகியும் மூணாவது மாசச் சம்பளத்தைக் கொடுக்கறதுக்கு முன்னே நான் ஒரு வெத்துவேட்டுன்னு கண்டுபிடிச்சதே இல்லே .

ஏன் இதற்கு முன் பார்த்த வேலையை விட்டு விட்டீர்கள் ..?
நீங்கள் ஏன் இந்த வேலைக்கு நேர்காணல் நடத்த வேண்டிய அவசியம் வந்ததோ.. அதே காரணத்துக்காகத்தான்..!

இந்த பதவியில் நீங்கள் எதிர்பார்க்கும் அம்சங்கள் என்ன..?

நல்ல சம்பளம் , 0 % வேலை, பக்கத்து சீட்டுல கண்ணுக்கு குளிர்ச்சியா ஒரு பெண் , நாட்டாமை பண்ண எனக்குக் கீழே ஒரு கூட்டம். அது போதும் .
Endrum
Venkat,

Social article - Software Companies firing employees

Often, we are hearing a frequent new about software down due to financial break down in US.
I got some information on recent happenings in software field.

HP Layoffs

HP 24000 Lay-offs - After acquiring EDS, HP is planning to reduce their work force and they planned to take out 24000+ employees from their company. It includes both higher level and lower level people.

Satyam 4500 Job cuts :
Due to lehman brothers closure, Satyam computers has just started giving pink slips to its employees, could potentially downsize its workforce by a whopping 4,500 employees. Recently, satyam head have informed their employees not to be panic and advising his people to work hard.

Wipro technologies started giving pink slips:
Wipro technologies has put about 4-5% of its workforce, about 2,400-3,000 employees, under the scanner for non-performance.
This is the cycle and everyone has to sustain this problem. If you see the past 10 years record of IT industry it got changed in 2-3 times majorly in 2003. Satyam and Wipro are service based companies if their client is going to the depth of loss then why they bare the loss it is obvious that they will fired the employees.
The recent IT Lay offs is mainly due US Market slowdowns. And as everyone of us knows that the market is never stable. Who knows that though it is down now, it may grow at a remarkable height.
This is once again the firing season for IT. Satyam Computers has just started giving pink slips to its employees and around 4,500 of its employees are scheduled to get the boot.

wipro firing
Wipro Technologies has put about 4-5% of its workforce or about 2,400-3,000 employees, under the scanner for non-performance.
tcs firing
In February, TCS had given pink slips to 500 non-performers.

IBM firing
IBM India also asked about 700 employees to leave, citing non-performance.

patani firing
In July, Patni computer systems fired 400 employees.
If the going is tough for these bigwigs, what about those in the lower rung? Mid tier companies are also doling out pink slips in all generosity! Hexaware has reportedly booted out 500 people and this does not include the 130 employees to be laid off due to the shut down of its office in Gurgaon. Ramco Systems laid off 142 employees.
Not true news on infy firing.


Reference:

http://www.alleyinsider.com/2008/9/hp-firing-24-600-employees-after-buying-eds-hpq-

http://hubpages.com/hub/Indian-IT-Companies-Infy-Wipro-Satyam-Cognizant-Accenture-IBM-Firing-News

http://topinews.com/story/title/wipro-firing-infyosys-firing-cts-firing--hp-firing--news

http://www.indyapulse.com/Blogs/View.asp?ArticleId=688&CatId=111

Kindly share your comments regarding this post.
Regards,
Venkatesan Prabu .J

Social article - Increase in price - In india

Increase in rates:

• Everywhere rates were increasing due to some of our people. They are ready to pay huge money for lemon juice too. I have seen some hotels were selling lemon juice at Rs. 20. Are we think about the money which we are spending is worth. (50 paise to 1 Rs Lemon + 1 Rs sugar +1 Rs water +1 Rs Electricity). Why dont we prepare a lemon juice in our home. Are we become too lethargic.
• Giving huge tips to servers in the hotel. They are expecting the same from the normal people and they are not ready to serve the normal people. Dont you think, we are taking our society to the wrong side. We will raise flags against bribes. But, every one of us were doing the same indirectly.
• For comfortness, we used to book and travel in A/C buses. Monday morning, I have to go to job and I should not feel tired. Sunday nights, all ticket rates got increased and normal people cant travel in ordinary deluxe bus too. We are ready to buy tickets in black and indirectly, most of us spoiled our society. You can ask me, Why venkat is pointing only software engineers. Its because, we are now becoming educated fools... for getting comfortness. Nowadays, Black tickets for movies were increasing. Even theatre people stopped to give tickets in counter. Inturn, they are arranging poeple and selling their tickets for good rates.
• Our people wish to see films only in Mayajaal, INOX and other big theatres. Atleast, we should think about wasting our percious money. Some of us will go rarely to watch good films. Some of our people will prefer only those hi-fi theatres. Because they need comfortness to sit for 3 hours. Please please think whether we are spending our money for worthful things.

Planning to buy a house in India :-( Check this URL,

http://venkattechnicalblog.blogspot.com/2008/10/social-article-buy-property-in-india.html

How we are going to stop this? Provide me more inputs on this topics?

Venkatesan Prabu .J

Social Article- Buy a Property in India

Hi Friends,

Its pretty new topic / its not a technical topic and no way related to my blog topics. But, to create an awareness among our people (particularly software engineers). I scheduled my time to analyse the facts and express the same in my own words.

People who are involved / affected:

• Mostly software engineers (70%) , people in very good position (Government job)(10%), business people (10%) and other people (10%).
• One day, I wish to buy some flowers for my wife and went to a lady(she is selling flowers in the road). I asked flowers for Rs.5. She told about the increase in flower rates and I will be getting very less flowers for Rs.5. I replied her back. "No, i need it for Rs. 5. Its more than enough for me." Immediately, she enquired about my profession, and I told, "I am a software engineer" Immediately, she replied me back "Boss, You are earning lots and lots of money. But, you are not ready to buy flowers" She gave some flowers and forced me to give Rs.10. Its not a huge amount for me, but once, if someone comes to know about our profession, i wondered to see the rates reaching peaks. Are we cursed like this?
• No one knows the pain and hard work behind our earnings. To Complete an engineering couse is not an easy job. Clearing all the subjects (arrears too)..and after getting a degree with first class distinction. I can remember those days (Carrying resume and requesting others for job - we need to get employee referral - First round , Second round, Third round of interviews). Many people will loose their opportunity in the final round. Its hard time for us to get a good job in software industry.
• After getting job, we need to survive in this ever changing IT field, daily we need to update our knowledge to survive in this world, need to face lot of junk politics, lots and lots of restriction in the name of cost cutting, no job surety, no proper healthy food (as we are modernised with fast foods and hifi junk foods), no proper timeline to work, cant adhere to particular technology, no proper growth, no proper recognition. Atlast, we become more flexible as a non technology cum non human being (Entire brain loaded with junk politics).
• No one thinks that, we (software engineers) dont have good money in hand to buy a single toilet room too. Its because with the help of banks we are buying houses. Do we have time to think, if my software job is gone . What I will do? How to repay the loans? We may think that, am having house right. I will sell the house and repay my loans. Then, Whats the reason for buying a house and stretching ourselves under pressure and huge headaches. What happen, If the rates went down. Now, In US market we can get a house for Rs.82,000. But, no one is there to buy that house. Do you think, it wont happen in india?

Increase in rents:

• Our software engineers will be seeing huge money in the initial stage - Fresher (an average of Rs.20,000 per month). Immediately, they will target to buy a good bike and ready to shift their residence near to their office. Mostly, they(Bachelor) will stay in sharing basis and they wont think about the rent (10,000/5). Obviously Rs.2000 is not a huge rent for them. This makes house owners to think more. Nowadays, house owners were targeting inviting software engineers to their houses.
• Once after marriage, software engineers will come to know about paying huge rents from their pockets. It becomes a huge headache for them after marriage. So, they are trying to marry a working women to survive. But, they dont find time to rethink about the reason behind. Its because of them, the rents got increased.

Reason for buying a house:

• Increase in rents.
• To avoid conflicts and pressure from the house owners to maintaining the
• Sense of satisfaction - We used to think that, if we have our own house. Then, we will get a satisfaction like, I've got settled in my life.
• Greediness, to get more income from the houses.
• To avoid lot of conflicts from neighbours.
• Very good investment for rich people.
• NRI were trying to invest their dollars in India.

Reason for reducing land values in india :

• Due to increase in interest rates. If we calculate the interest rates, How much am getting money and How much am repaying? You will surely get heart attack. Suppose am getting 20 Lakhs as loan amount from a bank. I need to pay around 50 to 60 Lakhs (Includes interest). Is it really worth? Instead, you can earnt / save / invest money and you can buy the same house with out any debts right . Why dont we think in that angle? Instead, unnecessarily we are paying more money to builders and banks.
• Employees fired from top software companies. Due to cost cutting, downsizing company size, merging of companies. Employees who earned lakhs become jobless on the next day.
• Due to increase in land rates, software engineers cant afford huge amount and they stopped buying houses.
• A small awareness started among our people about this land value increase.
• Everyone around us understand our mentality and playing based on this. We are ready to buy a house of worth 30 to 40 lakhs. Targeting this budget, builders started their plan of building houses 1200 SQ Ft to 2400 SQ Ft. Even, it may worth 25 lakhs, it will become 30 lakhs for software engineers. If we ask the reason, we will get a reply "Boss, You are earning too much money" Are we earning black money? or earning money with out any hard work"

My suggestions in buying a property in 2008:

• Please dont buy property now, because due to financial break down and problems in software industry. The land value will surely reduce further. Stay for a while, rethink twice thrice before buying a property.
• Next year, the rates will decrease further....... Lot of changes will happen soon.

Some URLs for your reference :

Chennai property bubble bursts, prices dip : http://www.ndtv.com/convergence/ndtv/story.aspx?id=NEWEN20080067248

Chennai properties will be reducing soon:

http://www.articlesbase.com/real-estate-articles/soaring-chennai-property-prices-boon-or-curse-493732.html

Buy a flat and get BMW free: (People should think about this offer. They will dream about the gift offered and forgot to think about the property price. The business people will increase the property rates like (Actual rate+BMW car rate) and they will sell their property easily.

http://indiarealestatemonitor.com/property-news/buy-flat-get-bmw-free/

Property market gone down due to US breakdown:

http://indiarealestatemonitor.com/property-news/indian-realty-wakes-up-to-the-us-meltdown/

Land value is now Rs. 15,000 per square feet against Rs. 40,000 for a similar property in 2007

Analysts predicts that, the land value will go further down to Rs.10,000 in next 6 months.

http://indiarealestatemonitor.com/property-news/affordable-housing-is-the-new-choice/

Realtors were slowly analysing the market and they are not ready to step ahead:

http://indiarealestatemonitor.com/property-news/realtors-advance-tax-payment-falls-down/

http://indiarealestatemonitor.com/property-news/realty-firms-consider-joint-development-for-construction-projects/

Feel free to post your comments and kindly share this URL to your friends and create an awareness among our people.

My article on increase in rates in India:

http://venkattechnicalblog.blogspot.com/2008/10/social-article-increase-in-price-in.html

Venkatesan Prabu .J

Update table with inner join with other table in sql server

I have see lots of people were querying about inner join used on update statement. Based on two tables, I need to do an update statement. Is it possible? OOPS, its possible. Let see the same through step by step.
I have created a table named employee,
create table employee(id int, name varchar(100))

Inserted some records in the table.
insert into employee values(1,'Venkat')
insert into employee values(2,'Suba')
insert into employee values(3,'Lakshmi')
insert into employee values(4,'Arun')
insert into employee values(5,'Karthi')
select * from employee

OOPs forgot to alter table employee
alter column id int not null
alter table employee add constraint prime primary key (id)
create table employeedetails(empid int, city varchar(200),designation varchar(10))
alter table employeedetails
alter column empid int not null
Am adding foreign key between these two tables,
alter table employeedetails add constraint foreignkeyforeign key (empid) references employee(id)

insert into employeedetails values(1,'Chennai','Engineer')
insert into employeedetails values(2,'Chennai','Engineer')
insert into employeedetails values(3,'Bangalore','Engineer')
insert into employeedetails values(4,'Newyork','Engineer')
select * from employeedetails

Lets see how to update the table using inner join with other table,
update a
set city ='Sydney' from employeedetails a inner join employee b on a.empid=b.id where b.[name]='Venkat'

Thanks and Regards,
Venkatesan Prabu .J

SQL Script to list down the strings with delimiter

I have see lot of queries asking for down the strings from comma seperated string or semicolon seperated string. Below is a superb function to acheive the same.

CREATE FUNCTION dbo.fnSplit(
@INPUTSTRING VARCHAR(8000) -- Actual string with comma delimeted

, @Delimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@Delimiter,@INPUTSTRING,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@INPUTSTRING,1,CHARINDEX(@Delimiter,@INPUTSTRING,0)-1))),
@INPUTSTRING=RTRIM(LTRIM(SUBSTRING(@INPUTSTRING,CHARINDEX(@Delimiter,@INPUTSTRING,0)+LEN(@Delimiter),LEN(@INPUTSTRING))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@INPUTSTRING) > 0
INSERT INTO @List SELECT @INPUTSTRING -- Put the last item in
RETURN
END
GO

The above code with create a function which will split the string based on its input parameter.

Lets execute the below query,

select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

The above query will fetch the output,

item

-------------------------

1

22

333

444

5555

666

Happy Learning!!!

Regards,

Venkatesan Prabu .J