SQLTeam.com | Weblogs | Forums

Trim string based on characters


#1

Hi,

I have strings like

WebApp - 10148
Smart - App - 1458

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
WebApp
Smart

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

Can anyone assist me sort this?


#2

You can use REVERSE to find the LAST Occurrence in a String

-- Find LAST Occurrence
DECLARE @MyString varchar(100)='ABC\DEFGHIJKLMNOPQRSTU\VWXYZ'
	, @strFind varchar(100) = '\' -- '
	, @intStartLocation int = 0
	, @intResult int
SELECT @intResult = CHARINDEX(@strFind, REVERSE(@MyString), @intStartLocation)
SELECT	[@intResult] = @intResult, 
	[Position] = LEN(@MyString)-@intResult+1, 
	CHAR(13)+CHAR(10)+'....!....1....!....2....!....3....!....4....!....5',
	CHAR(13)+CHAR(10)+@MyString,
	CHAR(13)+CHAR(10)
	+ CASE WHEN @intResult = 0
		THEN 'NOT FOUND [' + @strFind + ']'
		ELSE REPLICATE(' ', LEN(@MyString)-@intResult+1-1) + @strFind
		END

Result:

@intResult  Position
----------- -----------
6           23
....!....1....!....2....!....3....!....4....!....5
ABC\DEFGHIJKLMNOPQRSTU\VWXYZ
                      \

#3

SELECT LEFT(app, LEN(app) - CHARINDEX('-', REVERSE(app)))