-2

Anybody knows how to delete everything after third '#' ?

I tried it like this but it's not working:

declare @s varchar(200) = 'abcd#efg#hij#delete';
select left(@S, charindex('@', @S, charindex('@', CHARINDEX('@',@S)+1)+1)-1);

Results should be 'abcd#efg#hij without '#delete' Any suggestion?

1
  • 5
    Why are you looking for an '@' sign, when you should be looking for the '#'? Am I missing something here?
    – SS_DBA
    Oct 10 '16 at 16:41
1

My suggestion for these is to always include the charindex functions as part of your SELECT statement so you can see them as you go along.

Using this method you'll be able to see your results as you go.

declare @s varchar(200) = 'abcd#efg#hij#delete';

SELECT CHARINDEX('#',@s) AS [FirstIndex]
,CHARINDEX('#',@s,CHARINDEX('#',@s)+1) AS [SecondIndex]
,CHARINDEX('#',@s,CHARINDEX('#',@s,CHARINDEX('#',@s)+1)+1) AS [ThirdIndex]
,LEFT(@s,CHARINDEX('#',@s,CHARINDEX('#',@s,CHARINDEX('#',@s)+1)+1)-1) AS [Final];

Returns: 'abcd#efg#hij'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.