CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ruan du Preez
Ruan du Preez

Reputation: 99

Removing part of string after specific character

Good Day

I am trying to remove a part of a string after a specific Character. It almost works when I use the specific Query: 

LEFT(T1.ItemCode, CHARINDEX('-VASA', T1.ItemCode) - 1) AS 'Item Code

The problem I Have is that when I add the -1 at the end I get an error: Invalid length parameter passed on the LEFT or SUBSTRING function. When I remove it returns the Item Code but Adds that last '-' I am also trying to get rid off. This is an example of an item code I am trying to fix: 0C0002AC-GG-VASA = Without the '-1' I get 0C0002AC-GG- want it to return: 0C0002AC-GG

Thanks `

Upvotes: 0

Views: 1233

Answers (3)

ram_sql
ram_sql

Reputation: 404

The problem is due to few of your item code which may not have the word '-VASA' and you are searching its position and again doing -1 which is negative. so first check weather your word is having '-VASA' in it or not. like :

Case when CHARINDEX('-VASA', T1.ItemCode) >=1 Then LEFT(T1.ItemCode, CHARINDEX('-VASA', T1.ItemCode) - 1) Else T1.ItemCode End AS 'Item Code'

Upvotes: 1

Mikhail Timofeev
Mikhail Timofeev

Reputation: 2169

the problem is if you dont have -VISA in the T1.ItemCode: CHARINDEX('-VASA', T1.ItemCode) returns 0 and LEFT(T1.ItemCode, 0 - 1) gives error, because of negative value.

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this:

LEFT(T1.ItemCode, CHARINDEX('-VASA', T1.ItemCode + '-VASA') - 1) AS 'Item Code'

Upvotes: 1

Related Questions