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

Advertisements

#basic