Select all until first occurence of a character

问题:

If I have data in which the number of characters before a | are always different how can I write a SELECT statement to get all the characters up to a pipe character?

Sample data:

asdf|adkfdll|dd
asdkdkdk|da|d

回答1:

;WITH T(C) AS
(
SELECT 'asdf|adkfdll|dd' UNION ALL
SELECT 'asdkdkdk|da|d' UNION ALL
SELECT ''
)
SELECT  LEFT(C, CHARINDEX('|',C + '|') -1)
FROM T

回答2:

You could use charindex with substring:

select  substring(col1, 1, charindex('|',col1))
from    (
        select  'asdf|adkfdll|dd' as col1
        union all
        select  'asdkdkdk|da|d'
        ) as YourData
where   charindex('|',col1) > 0