I'm looking for a concise way to get the leading text of a column prior to an optional marker, on SQL Server 2008 (so no `IIF`). I have an `nvarchar` column which may or may not have a marker at some point in the text, and I need to return either the full column value (if there's no marker) or the bit prior to the marker (if there is). (It's not my schema, and I can't change it.)

I see these choices:

1. Do it with `CHARINDEX` and `SUBSTRING`, which is verbose and ugly (see below), or
2. Create a function to centralize and hide the ugliness, and use it (see below).

Is there a better, built-in option? And if so, what is it? (The answer to this question may well be "no.")

*(I thought [this answer](https://stackoverflow.com/a/7755624/157247) talking about `PARSENAME` might be an option, but because the marker is optional, it isn't really helpful, and doing that rather than creating a function seems a bit hacky to me anyway...)*

---

Example of Option #1:

```
SELECT
    CASE
        WHEN CHARINDEX('{}', data, 1) = 0 THEN
            data
        ELSE
            SUBSTRING(data, 1, CHARINDEX('{}', data, 1) - 1)
    END AS trimmedData
FROM ...

```

*Cough. Hack.*

---

Example of Option #2

```
SELECT dbo.StrUntil(data, '{}') as trimmedData
FROM ...

```

...where `dbo.StrUntil` is something like:

```
IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'FN' AND name = 'StrUntil')
   exec('CREATE FUNCTION [dbo].[StrUntil]() RETURNS INT AS BEGIN RETURN 0 END')
GO

ALTER FUNCTION [dbo].[StrUntil](
@str    nvarchar(max),
@substr nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    RETURN CASE
        WHEN CHARINDEX(@substr, @str, 1) = 0 THEN
            @str
        ELSE
            SUBSTRING(@str, 1, CHARINDEX(@substr, @str, 1) - 1)
    END
END

```