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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s