/************************************/
/* GRUMPY PODCAST - STRNG FUNCTIONS */
/************************************/

USE [TEST]
GO

/*****************************/
/* TOPIC 01 - LEN() FUNCTION */
/*****************************/

DECLARE @AString VARCHAR(256);

SET @AString = 'This is a fairly long and boring string!'

SELECT 
	'The length of this string is: ' + 
	CONVERT(VARCHAR,LEN(@AString)) + 
	' characters.'
GO

/******************************/
/* TOPIC 02 - LEFT() FUNCTION */
/******************************/

DECLARE @AString VARCHAR(256);

SET @AString = 'This is a fairly long and boring string!'

SELECT 'The first 10 characters of this string are: ' + CONVERT(VARCHAR,LEFT(@AString,10))
GO

DECLARE @AString VARCHAR(256);

SET @AString = 'This is a fairly long and boring string!'

SELECT 'The first 4 characters of this string are: ' + CONVERT(VARCHAR,LEFT(@AString,4))
GO

/*******************************/
/* TOPIC 03 - RIGHT() FUNCTION */
/*******************************/

DECLARE @AString VARCHAR(256);

SET @AString = 'This is a fairly long and boring string!'

SELECT 'The last 7 characters of this string are: ' + CONVERT(VARCHAR,RIGHT(@AString,7))
GO

/*******************************/
/* TOPIC 04 - LTRIM() FUNCTION */
/*******************************/

DECLARE @AString VARCHAR(256);

SET @AString = '  This is a fairly long and boring string!'

--'  This is a fairly long and boring string!'
--'This is a fairly long and boring string!'

SELECT @AString;

SELECT LTRIM(@AString);

SELECT 'After removing leading blanks the string is: ' + LTRIM(@AString)
GO

/*******************************/
/* TOPIC 05 - RTRIM() FUNCTION */
/*******************************/

DECLARE @AString VARCHAR(256);

SET @AString = 'This is a fairly long and boring string!           '

SELECT @AString;

SELECT RTRIM(@AString);

SELECT 'After removing trailing blanks the string is: ' + RTRIM(@AString)
GO

DECLARE @AString VARCHAR(256);

SET @AString = '            This is a fairly long and boring string!                   ';

SELECT @AString;

--'            This is a fairly long and boring string!                   '
--'This is a fairly long and boring string!'

SELECT LTRIM(RTRIM(@AString));

SELECT 'After removing leading & trailing blanks the string is: ' + LTRIM(RTRIM(@AString))
GO

/***********************************/
/* TOPIC 06 - CHARINDEX() FUNCTION */
/***********************************/

DECLARE @AString VARCHAR(256);
DECLARE @SpacePos SMALLINT;
DECLARE @Word VARCHAR(64);

SET @AString = 'This is a boring string!'
SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);
SELECT 'The word is: ' + @Word;

SET @AString = RIGHT(@Astring,LEN(@Astring) - @SpacePos)

SELECT @Astring;

SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);
SELECT 'The word is: ' + @Word;

SET @AString = RIGHT(@Astring,LEN(@Astring) - @SpacePos)
SELECT @Astring;

SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);
SELECT 'The word is: ' + @Word;

SET @AString = RIGHT(@Astring,LEN(@Astring) - @SpacePos)
SELECT @Astring;

SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);
SELECT 'The word is: ' + @Word;

SET @AString = RIGHT(@Astring,LEN(@Astring) - @SpacePos)
SELECT @Astring;
GO

/***********************************/
/* TOPIC 07 - SUBSTRING() FUNCTION */
/***********************************/

/*******************************************************/
/* We are going to substitute the RIGHT()function with */
/* the SUBSTRING() function.                           */
/*******************************************************/

DECLARE @AString VARCHAR(256);
DECLARE @SpacePos SMALLINT;
DECLARE @Word VARCHAR(64);

DECLARE @WordTable TABLE (
	Word VARCHAR(64)
);

SET @AString = 'This is a boring string!'
SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);

SELECT 'The word is: ' + @Word;

INSERT INTO @WordTable VALUES(@Word);

SET @AString = SUBSTRING(
	@Astring,
	@SpacePos + 1,
	LEN(@Astring) - @SpacePos
	)

SELECT @Astring;

SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);

INSERT INTO @WordTable VALUES(@Word);

SELECT 'The word is: ' + @Word;

SET @AString = SUBSTRING(
	@Astring,
	@SpacePos + 1,
	LEN(@Astring) - @SpacePos
	)

SELECT @Astring;

SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);

SELECT 'The word is: ' + @Word;

INSERT INTO @WordTable VALUES(@Word);

SET @AString = SUBSTRING(
	@Astring,
	@SpacePos + 1,
	LEN(@Astring) - @SpacePos
	)
SELECT @Astring;

SET @SpacePos = CHARINDEX(' ',@AString,1)

SELECT 'Found a space character at position: ' + CONVERT(VARCHAR,@SpacePos);

SET @Word = LEFT(@AString,@SpacePos - 1);

SELECT 'The word is: ' + @Word;

INSERT INTO @WordTable VALUES(@Word);

SET @AString = SUBSTRING(
	@Astring,
	@SpacePos + 1,
	LEN(@Astring) - @SpacePos
	);

SELECT @Astring;

INSERT INTO @WordTable VALUES(@Astring);

SELECT * FROM @WordTable
GO

/*********************************/
/* TOPIC 08 - REPLACE() FUNCTION */
/*********************************/

DECLARE @AString VARCHAR(256);

SET @AString = 'This is a fairly long and boring string!  '

SELECT 'Now the string is more interesting: ' + REPLACE(@AString,'boring','interesting')
GO