You could also create a function to do it:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnSplitList]
(
@sInputList VARCHAR(8000),
@sDelimiter VARCHAR(10)
)
RETURNS @List TABLE ( item VARCHAR(8000) )
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN
SELECT @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1,
CHARINDEX(@sDelimiter,
@sInputList, 0)
- 1))) ,
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList,
CHARINDEX(@sDelimiter,
@sInputList, 0)
+ LEN(@sDelimiter),
LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List
SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List
SELECT @sInputList -- Put the last item in
RETURN
END
GO
From there you just do the following:
SELECT * FROM dbo.fnSplitList('7:00 AM delimiter 12:30 PM','delimiter')
Output :
item
7:00 AM
12:30 PM
You can then be flexible on your delimiters 🙂