-- CREATE TABLE Address (State char(2))
-- 
-- INSERT Address VALUES ('MN')
-- INSERT Address VALUES ('ND')
-- INSERT Address VALUES ('SD')
-- INSERT Address VALUES ('NE')
-- INSERT Address VALUES ('KS')
-- INSERT Address VALUES ('OK')

-- CREATE FUNCTION dbo.udfGetSequence
--    (@A int,
--     @Z int)
-- RETURNS @T TABLE (Number int)
-- AS
-- BEGIN
-- 
-- DECLARE @I int
-- 
-- SET @I = 1
-- 
-- INSERT @T VALUES (@A)
-- 
-- WHILE @I < @Z - @A + 1
-- 
--     BEGIN
-- 
--     INSERT @T
--     SELECT @I + Number
--       FROM @T
--      WHERE @I + Number <= @Z
-- 
--     SET @I = @I + @@ROWCOUNT
-- 
--     END
-- 
-- RETURN
-- END

-- CREATE FUNCTION dbo.udfCountString
--    (@S varchar(8000),
--     @E varchar(  80))
-- RETURNS smallint
-- AS
-- BEGIN
-- 
-- DECLARE @Result smallint
-- 
-- DECLARE @I smallint
-- 
-- SET @I = DATALENGTH(@S)
-- 
-- SELECT @Result = COUNT(*)
--   FROM udfGetSequence(1,@I) AS T
--  WHERE SUBSTRING(@S,T.Number,@I) LIKE @E + '%'
-- 
-- RETURN (@Result)
-- END

-- CREATE FUNCTION dbo.udfParseString
--    (@S varchar(7998),
--     @E    char(   1))
-- RETURNS @T TABLE (Number smallint IDENTITY(1,1), Offset smallint, String varchar(2000))
-- AS
-- BEGIN
-- 
-- DECLARE @N smallint
-- 
-- DECLARE @Z varchar(8000)
-- 
-- SET @N = LEN(@S) + 1
-- 
-- SET @Z = @E + @S + @E
-- 
--   INSERT @T (Offset, String)
--   SELECT Number
--        , SUBSTRING(@S,Number,CHARINDEX(@E,@Z,Number+1)-Number-1)
--     FROM dbo.udfGetSequence(1,@N)
--    WHERE SUBSTRING(@Z,Number,1) = @E
-- ORDER BY Number
-- 
-- RETURN
-- END

SELECT * FROM Address CROSS JOIN dbo.udfGetSequence(1,6) WHERE Number < CASE State WHEN 'NE' THEN 4 WHEN 'KS' THEN 4 WHEN 'OK' THEN 4 ELSE 7 END

SELECT * FROM Address, dbo.udfGetSequence(1,6) WHERE State NOT IN ('NE','KS','OK') OR Number < 4

DECLARE @S varchar(2000)

SET @S = 'football basketball baseball hockey soccer volleyball golf tennis'

SELECT dbo.udfCountString(@S,'ball')
-- SELECT dbo.udfCountString(@S,'a')
-- SELECT dbo.udfCountString(@S,'z')

DECLARE @S varchar(2000)

SET @S = 'This fine line is a baseline for the incline of the main line.'

SELECT dbo.udfCountString(@S,'[ ]line[;,.!? ]')

DECLARE @S varchar(2000)

SET @S = '1,12,123,1234,ABCD,ABC,AB,A,*'

SELECT * FROM dbo.udfParseString(@S,',')