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:

     LTRIM(String)
     RTRIM(String)

     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.

     Syntax:

 -- 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   

 
       @tablevariable 
      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.  

      Syntax:

 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  
 )  

    Way2: 
   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  
    FROM  
     (  
       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)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS


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' ] )
SELECT QUOTENAME('Sql[]String')
-- 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)
SELECT REPLICATE('Sql', 2)
-- Value = SqlSql


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


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


Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE('SQLServer')
-- Value = revreSLQS


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


Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER('SQLServer')
-- 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)