CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsplit
Michael
Michael

Reputation: 305

Trim string based on characters

I have strings like 

WebApp - 10148

Smart - App - 1458

Expected Result

WebApp

Smart - App

I want to trim the characters from - to numbers from right.

I have tried the below query and the result is this 

select LEFT(app+' - ', CHARINDEX(' - ',app+' - ')-1) from repository

WebApp
Smart

Can anyone assist me to sort this?

Upvotes: 3

Views: 97

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Just for fun one approach with rekursive CTE (I'd use Rahul's answer :-) )

DECLARE @tbl TABLE(x VARCHAR(100));
INSERT INTO @tbl VALUES('WebApp - 10148'),('Smart - App - 1458');

WITH FindHyphens AS
(
    SELECT x,0 AS StartInx, PATINDEX('% - %',x) AS PosHyphen,SUBSTRING(x,PATINDEX('% - %',x)+1,1000) AS Subst FROM @tbl
    UNION ALL
    SELECT x,StartInx+PATINDEX('% - %',Subst),PATINDEX('% - %',Subst),SUBSTRING(Subst,PATINDEX('% - %',Subst)+1,1000)
    FROM FindHyphens
    WHERE PATINDEX('% - %',Subst)>0
)
,FindLastHyphen AS
(
    SELECT x, MAX(StartInx+PosHyphen)-1 AS LastPos
    FROM FindHyphens
    GROUP BY x
)
SELECT SUBSTRING(x,1,LastPos)
FROM FindLastHyphen

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

Try this:

reverse(right(reverse(@string), len(@string) - charindex('-',reverse(@string),1)));

Upvotes: 2

Related Questions