MS-SQL 2008

Sql 2008 Forums 

a)  How can I use LTRIM/RTRIM to search and replace leading/trailing spaces?

    -  SQL Server does not have function which can trim leading or trailing spaces of any string at the same            time. SQL does  have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively.          SQL Server 2008 also does not   have TRIM() function. User can easily use LTRIM() and RTRIM()            together and simulate TRIM() functionality.The syntax for the functions are as follows:
     The String parameter that is passed to the functions can be a column name, a variable, a literal string or         the output of a user defined function or scalar query.
SELECT LTRIM('    spaces at start')

SELECT RTRIM(FirstName) FROM Customers
      If you want to remove the white spaces at both the beginning and end of the string then you can simply           apply both LTRIM and RTRIM to the string. Alternatively if you are using MySQL or Microsoft                   Access then you can use the TRIM function which does the same thing as applying both LTRIM and           RTRIM.

-- SQL Server / Oracle
UPDATE Customers 
SET FirstName = LTRIM(RTRIM(FirstName))

-- Access / MySQL
UPDATE Customers 
SET FirstName = TRIM(FirstName)

b) What is #temp table and @table variable in SQL Server?
    - #tempTable(TemporaryTable) 

     temp table is a temporary table that is generally created to store session specific data. Its          kind of normal table but it is created and populated on disk, in the system database tempdb        with a session-specific identifier packed onto the name, to differentiate between similarly-          named #temp tables created from other sessions.

     The data in this #temp table (in fact, the table itself) is visible only to the current scope.            Generally, the table gets cleared up automatically when the current procedure goes out of          scope, however, we should manually clean up the data when we are done with it.


 -- create temporary table  
 CREATE TABLE #myTempTable (  
 AutoID int,  
 MyName char(50) )  
 -- populate temporary table  
 INSERT INTO #myTempTable (AutoID, MyName )  
 SELECT      AutoID, MyName   
 FROM      myOriginalTable  
 WHERE      AutoID <= 50000  
 -- Drop temporary table  
 drop table #myTempTable   

      table variable is similar to temporary table except with more flexibility. It is not physically             stored in the hard disk, it is stored in the memory. We should choose this when we need to         storeless100records.  


 DECLARE @myTable TABLE (  
 AutoID int,  
 myName char(50) )  
 INSERT INTO @myTable (AutoID, myName )  
 SELECT      YakID, YakName  
 FROM      myTable  
 WHERE      AutoID <= 50  

 c)  What is Subquery in SQL Server?
    - A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement,      or inside another subquery. Subquery is an inner query or inner select, while the statement          containing a subquery is also called an outer query or outer select. 
 SELECT CustName, CustOrderDate, (SELECT DateTimeOrdered FROM OrderDetails as ODetails WHERE Ord.SalesOrderID = ODetails.SalesOrderID) AS OrderedDateTime FROM Orders AS Ord  

     For example

     d)  How will you delete all records of table other then recently added 10 records?
    - There can be many ways to get the desired results. Two of the simplest ways are described         below assuming your table is having column(date1) containing value of data creation. 
       Way 1: 
      With the query below, we are deleting all the records except 10 records where id is not               matching with the top most listed records based on time entry.

 delete from table1  
 where id1 not in  
      select top 10 id1 from table1  
      order by date1 desc  
   With this query we are listing records in descending way with date as key and having index          more then 10. All listed records will be deleted.

 delete from table1  
 WHERE id1 in (  
    SELECT id1  
       SELECT id, ROW_NUMBER() OVER(ORDER BY date1 DESC) AS rownumber  
       FROM table1  
     ) AS a  
    WHERE rownumber > 10  

Using Sql server's useful Functions

This article explains the functionality and uses of the LEFT, RIGHT, SUBSTRING and CHARINDEX functions in SQL.

Example SQL String Function - SPACE
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)

Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial')
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20)

-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)

Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function

Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] )
-- Value = [Sql[]]String]

Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 )
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning

Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer)
SELECT LEFT('TravelYourself', 6)
-- Value = Travel
SELECT LEFT('BeautyCentury',6)
-- Value = Beauty

Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)
-- Value = urself

SELECT RIGHT('BeautyCentury',6)
-- Value = Century

Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)

-- Value = SqlSql

Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
-- Value = Ser

Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string)
-- Value = 9

Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)

-- Value = revreSLQS

Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char)
-- Value = 83 (it take first character)
-- Value = 83

Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )

-- Value = sqlserver

Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string )
SELECT UPPER('sqlserver')
-- Value = SQLSERVER

Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(' sqlserver')
-- Value = 'sqlserver' (Remove left side space or blanks)

Example SQL String Function - RTRIM
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
SELECT RTRIM('SqlServer ')

-- Value = 'SqlServer' (Remove right side space or blanks)

No comments:

Post a Comment

Twitter Bird Gadget