Wednesday, August 24, 2011

System Function in SQL-Server

Posted by Rahul Kharde at 10:15 PM

GETDATE() Gives current date time (server’s time zone)

E.g. SELECT GETDATE()return 2011-08-25 09:54:39.963


GETUTCDATE() Gives current date time (GMT + 0 Time zone)

E.g. SELECT GETUTCDATE() return 2011-08-25 04:27:30.163


ISDATE('Value') Returns bool value whether given value is proper date or not

E.g. SELECT ISDATE('2011-08-25') return 1

SELECT ISDATE('2011-13-25') return 0


LTRIM('Value') Trims from left

E.g. SELECT LTRIM(' SQL SERVER ') return ‘SQL SERVER


RTRIM('Value') Trims from right

E.g. SELECT LTRIM(' SQL SERVER ') return ‘ SQL SERVER‘


REPLACE() replace all occurrence of string with other string

E.g. SELECT REPLACE('SQL SERVER ','S','A') return ‘AQL AERVER


ISNUMERIC('Value') Checks whether given value is numeric or not. Gives Boolean result

E.g. SELECT ISNUMERIC(3) return 1

SELECT ISNUMERIC(‘SQL’) return 0


LEN('Value') Gives total number of characters available in given string (after right trimming)

E.g. SELECT LEN('SQL SERVER') return 10


UPPER('Value') Converts all the characters into upper case

E.g. SELECT UPPER('SqL SErVeR') return ‘SQL SERVER’


LOWER('Value') Converts all the characters into lower case

E.g. SELECT LOWER('SqL SErVeR') return ‘sql server’


SUBSTRING() Gives substring from string with specific position

E.g. SELECT SUBSTRING('SQL SERVER',2,3) return ‘QL’


REVERSE('Value')Gives reverses the string

E.g. SELECT REVERSE('SQL SERVER') return ‘REVRES LQS’

CHARINDEX() Gives position of “characters” in a string starting from specific position

E.g. SELECT CHARINDEX('S','SQL SERVER',2) return 5


SCHEMA_ID('Value') Gives ID of a given schema

E.g. SELECT SCHEMA_ID('dbo') return 1


SCHEMA_NAME('Value') Gives name of a schema with given ID

E.g. SELECT SCHEMA_NAME('1') return 0


SCOPE_IDENTITY()returns the last identity value generated for any table in the current session and the current scope

E.g. SELECT SCOPE_IDENTITY()


IDENT_CURRENT('TableName') returns the last identity value generated for a specific table in any session and any scope

E.g. SELECT IDENT_CURRENT('emp')


ISNULL(ColumnName,'Value') Replaces NULL value of a given column with passed string

E.g. SELECT ISNULL(NULL,'SQL') return SQL

SELECT ISNULL(NULL,NULL)return NULL




If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top