SQL Server Basic – II


--Selecting part of a composite field  
Select SUBSTRING('Mr. ' + au_fname ,1,5 )  + '  ' + au_lname FROM Authors 

--A Combination of Upper, Lower, LEN and Substring  

Select AllSmallFirstName = LOWER(au_fname), 
 InitCapFirstName = UPPER(LEFT(au_fname,1)) + LOWER(SUBSTRING(au_fname ,2,LEN(au_fname))) 
 FROM Authors 
 
--A Combination of Upper, Lower, Left and Substring  
Select LEFT(UPPER(au_fname),1) +LOWER(SUBSTRING(au_fname ,2,LEN(au_fname))) 
+ ' ' +  UPPER(SUBSTRING(au_lname ,1,1 ))  AS SHORTNAME FROM Authors

--Replacement of a part of a field or expression
SELECT REPLACE('United States has 50 states. Canada is north of the United States',
 'United States', 'U.S.A.')

--Substituting a character in an expression
select REPLACE(city,'k','x') CITY from Authors

--Substituting a separator in an expression
SELECT REPLACE('flowers,bells,glasses,linen,confettii',',',' | ')
SELECT REPLACE('21.9.1999','.','/')

--Substituting a character in an expression
select Phone,REPLACE(REPLACE(phone,' ','.'),'-','.') NewPhone from Authors
 
--Repeatition of content of a field
SELECT REPLICATE(au_fname, 3)  as STUFF 
 FROM authors 
 ORDER BY STUFF 

--Repeatition of content of a field
SELECT REPLICATE('-', 100)  AS LINE 

--Repeatition of content of a field together with replacing few characters
SELECT REPLACE(REPLICATE('', 2),'></')   AS PATTERN

--Padded money representation
Select 'Total Invoice Amount : ' + REPLICATE('*',(12 - LEN('638.99'))) + '638.99' + 'USD'

Select 'Total Invoice Amount : ' + REPLICATE('*',(12 - LEN('363638.99')))
 + '363638.99' + 'USD'

 --Right Shift of Binary Zeroes
SELECT 
 REPLICATE('0',(8 - LEN('1'))) + '1'
,REPLICATE('0',(8 - LEN('11'))) + '11'
,REPLICATE('0',(8 - LEN('111'))) + '111'
,REPLICATE('0',(8 - LEN('1111'))) + '1111'
,REPLICATE('0',(8 - LEN('11111'))) + '11111'
,REPLICATE('0',(8 - LEN('111111'))) + '111111'
 
--Reversing of content of a field

SELECT REVERSE(au_fname) 
 FROM authors 
 ORDER BY au_fname 

--Pallindroming  a field
SELECT au_fname  + ' ' + REVERSE(au_fname) PALLINDROME FROM authors ORDER BY au_fname 

--Blank spaces between fields
Select au_fname + SPACE(2) + au_lname From Authors

--Replacing a pattern with blanks
select REPLACE('New \t Light','\t',SPACE(10))

--Replacing a separator with spaces
select REPLACE('Tom|Jerry|McInTyre','|',SPACE(10))

SQL Server Basic – I

These articles will help beginners to understand the Basic SQL queries…

--Selecting static non database text  
Select 'Hello World!' 

--Selecting the database and a dummy select
Use Pubs   
Select null

--Selecting every column content  
Use Pubs   
Select *  from authors  

--Selecting a mix of static text and every column content  
Select 'Row>',*  from authors  

--Seeing the definition of a table.
Sp_help Authors  

--Seeing the definition of a system table.
Sp_help SysObjects  

--Selecting a column content
Select au_fname from authors

--Converting a column content  to uppercase
Select UPPER(au_fname) from authors

--Converting a column content  to lowercase
Select LOWER('September 2002 | SQL Server Savvy |
 SQL Server Magazine A Set-Based Way to Find   Specific Rows Solutions')

--Selecting a column's First character  
Select LEFT(au_fname,1) from authors 

--Selecting a column's First 5 characters character  
Select LEFT(au_fname,5) from authors  


--Selecting a column's Last 5 characters character  

Select '%' + RIGHT(au_fname,5) from authors  

--Selecting a column's Length 
Select LEN(au_fname) from authors  
GO
Select len(727272727), datalength(727272727)
GO

--Parsing a US phone number string  
SELECT Phone, left(Phone,3)  AreaCode ,
left(right(Phone,8),3) Exchange ,
right(Phone,4) Connection from  Authors

--Finding initial characters of columns and adding them together
Select (upper(left(au_fname,1)))+ '.' 
+ upper(left(au_lname,1)) + '.' 
as Initials from Authors

--Selecting a column's content, its length and give new header aliases to each  

Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors

--Selecting a column's content, its length and giving new header aliases to each . 
--Also Ordering the output of Names Alphabetically using field name
Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors   
Order by au_fname
 
--Do as above using the Header Alias for ordering  
--Yields same results as above
Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors   
Order by AUTHOR_FIRSTNAME  


--Do as above using the Column Index for ordering  
--Yields same results as above  
Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors   
Order by 1  

--Ordering the results using column indexes.   
Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors   
Order by 2  

--Ordering the results using reverse alphabetical ordering  
Select au_fname as AUTHOR_FIRSTNAME, LEN(au_fname) AS LENGTH_FIRSTNAME from authors   
Order by 1 DESC  

--Multiple Ordering, natural preferences  

Select au_fname as FirstName, state from Authors   
 Order by 1 ASC, 2 ASC  

--Multiple Ordering ,  Mixed Preferences  

Select au_fname as FirstName, state from Authors   
 Order by 1 DESC, 2 ASC  
  

--Multiple Ordering ,  Order preference given to later fields in the select list  

Select au_fname as FirstName, state from Authors   
 Order by 2 ASC, 1 ASC  

--Derived Fields &  their Ordering  

Select au_fname + '  ' + au_lname as [AUTHOR'S FULL NAME]   
 FROM Authors  
 Order by [AUTHOR'S FULL NAME]  

--Derived Fields &  their Ordering continued  
Select  'Mr. ' + au_fname + ' - ' + au_lname as [AUTHOR'S FULL NAME]    
 FROM Authors   
 Order by [AUTHOR'S FULL NAME]   

--Selecting part of a field  
Select SUBSTRING('System Statistical Functions',7,12) STUFF

--Selecting part of a field  

Select SUBSTRING( au_fname, 2,5)   
 FROM Authors   
  
  
--Selecting part of a composite field  
Select SUBSTRING(au_fname ,1,1 )  + '  ' + au_lname FROM Authors