This website completely moved to new platform. For latest content, visit www.programmingposts.com

Search this Site

10 Nov 2012

STRING FUNCTIONS IN SQL SERVER


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SELECT Ascii('a') 

SELECT Ascii('A')  /*RETURNS ASCII VALUE OF CHARACTER A*/

SELECT Char(97) 

SELECT Char(65) /*RETURNS CHARACTER OF THE GIVEN ASCII VALUE*/ 

SELECT Charindex('M', 'SAMEER', 1) 

SELECT Charindex('M', 'SAMEER') /*RETURNS THE INDEX OF M IN SAMEER*/ 

SELECT LEFT('SAMEER', 3) 

SELECT RIGHT('SAMEER', 3) 

SELECT Substring('SAMEER', 0, 3) /*RETURNS SA*/ 

SELECT Substring('SAMEER', 1, 3) /*RETURNS SAM*/ 

SELECT Len('SAMEER') /*RETURNS LENTH OF THE STRING*/ 

SELECT Lower('SamEEr') /*DISPLAYS STRING IN ALL LOWERE CASE LETTERS*/ 

SELECT Upper('SamEEr') /*DISPLAYS STRING IN ALL UPPER CASR LETTERS*/ 

SELECT Ltrim('         SAMEE R   .. ') /*REMOVES SPACES OF THE LEFT SIDE OF STRING*/ 

SELECT Rtrim('..     SA M EE R       ') /*REMOVES SPACES OF THE RIGHT SIDE OF STRING*/ 

SELECT Replace('SAMEER', 'R', 'E') /*REPLACES R WITH E IN THE GIVEN STRING*/ 

SELECT Replicate('SAM ', 3) /*THIS QUERY PRINTS SAM FOR 3 TIMES*/ 

SELECT Reverse('SAMEER') /*PRINTS REVERSE OF THE STRING*/ 

SELECT Difference('SAMEER', 'SAMIR') /*RATES THE SIMILARITY RETURNING 0 THROUGH 4*/ 

SELECT 'SHAIK' + Space(5) + 'SAMEER' /*PRINTS SPACE BETWEEN 2 STRINGS*/ 

SELECT Soundex('SAMEER'), 
       Soundex('SAMIR'), 
       Difference('SAMEER', 'SAMIR') /*DISPLAY SIMILARITY BETWEEN 2 STRINGS WITH A NUMBER/VALUE*/ 

SELECT Stuff('SAA33MEE77R', 4, 2, '99') /*DISAPPERS 2 CHARACTERS FROM THE INDEX 4 AND REPLACES WITH 99*/