SQL Server Basic – III

–Embedding incremental spacing between characters in a literal
Select Replace (‘abababa’,’b’,SPACE(0)) away

Select Replace (‘abababa’,’b’,SPACE(1)) away

Select Replace (‘abababa’,’b’,SPACE(2)) away

Select Replace (‘abababa’,’b’,SPACE(3)) away

Select Replace (‘abababa’,’b’,SPACE(4)) away

–Replacing spaces with more spaces
Select Replace (‘The Code of Nimrods  2’,SPACE(1),SPACE(10))
Select Replace (‘The Code of Nimrods  2’,CHAR(32),SPACE(10))

–Replacing spaces with a pattern
Select Replace (‘The wind is getting chilly’,SPACE(1),CHAR(45)) Hyphoon ,

Replace (‘The wind is getting chilly’,SPACE(1),CHAR(47)) Slasher ,

Replace (‘The wind is getting chilly’,SPACE(1),CHAR(95)) LowerDraw ,

Replace (‘The wind is getting chilly’,SPACE(1),CHAR(124)) DigPipe,

Replace (‘The wind is getting chilly’,SPACE(1),’ ‘) Html_Space

–Replacing spaces with a newline
SELECT Replace (‘The wind is getting chilly’,SPACE(1),CHAR(13)) Chinese

–A report like formatting with single SQL
SELECT au_fname + CHAR(32) + Au_lname + CHAR(32) + Phone +
CHAR(13) + Address + CHAR(13) + City + CHAR(32) + state +
CHAR(32) + Zip + replicate(CHAR(13),3) from Authors

–Drawing decorative lines
select Replicate (‘~o~’+SPACE(4),50)

–Stripping of Leading Spaces
select LTRIM(‘     Five spaces are at the beginning of this string.’)

–Stripping of Leading Spaces
select ‘<‘ +  ltrim(‘    middle    ‘) + ‘>’

–Drawing decorative lines
select replicate(space(9)+’———‘,10)
select LTRIM (replicate(space(9)+’———‘,10))

–Stripping of Trailing Spaces
select RTRIM(‘Five spaces are at the end of this string.     ‘)

–Stripping of Trailing Spaces
select ‘<‘+LTRIM(RTrim(‘    middle    ‘)) +’>’

–A mix of Space trimming
select ‘<‘+Type+’>’ WITH_TRAILING_SPACES,'<‘+LTRIM(RTrim(Type)) +
‘>’ LACKING_SPACES from Titles

Advertisements

#sql-basic