SQL_Enthusiast (5/7/2013)
I did read BOL, along with 50 other articles that really didn't help. So I figured it out on my own. I'm open to suggestions on how to make it better, but for now... it works so I'm smiling 😀
SELECT
[tt].[GROUP_NUM]
, CASE WHEN [tt].[GROUP_NUM] LIKE '%~%'
THEN SUBSTRING([tt].[GROUP_NUM] , 1 ,
CHARINDEX('~' , [tt].[GROUP_NUM]) - 1)
END AS 'LEFT'
, CASE WHEN [tt].[GROUP_NUM] LIKE '%~%'
THEN (SELECT RIGHT([tt].[GROUP_NUM], CHARINDEX('~', REVERSE([tt].[GROUP_NUM])) -1))
END AS 'RIGHT'
FROM
[dbo].[TEMP_TABLE] AS tt
I would make it differently, but not sure I could make it much better. 🙂
Only note - your CASE statements are missing ELSE parts.
If there is no ~ in the string both LEFT and RIGHT parts will be NULL - is it as intended?
_____________
Code for TallyGenerator