Use PARSENAME, CHARINDEX, PATINDEX or SUBSTRING To Grab Values Up To A Certain Character

This is a question that came up yesterday in the Getting started with SQL Server MSDN forum (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=353250&SiteID=1)
A person wanted to use a MID function in SQL Server, There is no MID function in SQL Server but there are at least 4 ways to implement what the person tried to accomplish
Basically if the data looked like this

aaa-bbbbb
ppppp-bbbbb
zzzz-xxxxx

Then we need to grab everything up until the minus sign
So let's get started

CREATE TABLE #Test (
SomeField VARCHAR(49))

INSERT INTO #Test
VALUES ('aaa-bbbbb')

INSERT INTO #Test
VALUES ('ppppp-bbbbb')

INSERT INTO #Test
VALUES ('zzzz-xxxxx')

--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test

--using LEFT and CHARINDEX
SELECT LEFT(SomeField,CHARINDEX('-',SomeField) - 1)
FROM #Test

--using LEFT and PATINDEX
SELECT LEFT(SomeField,PATINDEX('%-%',SomeField) - 1)
FROM #Test


--using CASE SUBSTRING and LEFT
SELECT CASE SUBSTRING(SomeField,4,1)
WHEN '-' THEN LEFT(SomeField,3)
ELSE LEFT(SomeField,4)
END
FROM #Test


--clean up
DROP TABLE #Test