USE OLTP
--Let's have a look at the product table
SELECT * FROM products

--We have a function called charindex() to find the location of a specific character
SELECT charindex('-','String to test - our function on') as result
go
CREATE FUNCTION findstringto(@string nvarchar(2000),@endchar nvarchar(1)) returns nvarchar(2000) as
--function to return text up to an @endchar
BEGIN
	return dbo.getstring(@string,1,charindex(@endchar,@string)-1)--stop 1 before our delimiter
END
go

SELECT dbo.findstringto('String to test - our function on','-') as result

go
CREATE FUNCTION findstringfrom(@string nvarchar(2000),@startchar nvarchar(1)) returns nvarchar(2000) as
--Function to return a string after a @startchar
BEGIN
	return dbo.getstring(@string,charindex(@startchar,@string)+1,len(@string))--start 1 after our delimiter
END
go

SELECT dbo.findstringfrom('String to test - our function on','-') as result

--Now we can use both of these functions to split our product_type and product column into two columns
go
SELECT Code, Product_line
       ,dbo.findstringto([Product_type/Product],'/') as product_type
	   ,dbo.findstringfrom([Product_type/Product],'/') as product
	   ,Product_cost
FROM products

--We can also create a function that removes Non-printable characters from a string
go
CREATE FUNCTION clean (@string nvarchar(2000)) RETURNS nvarchar(2000) AS
--Remove non-printable characters
BEGIN
	return (
		REPLACE(
		REPLACE(
		REPLACE(
		REPLACE(
		REPLACE(@string,char(9), '')--horizontal tab
		,char(10), '') --Line Feed
		,char(11), '') --vertical tab
		,char(12), '') --Form Feed
		,char(13), '') --Carriage Return
	)
END
go

--Test
SELECT dbo.clean('This string contains a carriage return
 followed by serveral tabs			 and the finishes here.') as clean_string