CODEWITHSUNDEEP
sqlsql-server
user357034
user357034

Reputation: 10981

sql query to remove prefix from field

Given the following

Products_Joined.ProductName AS [stripHTML-TITLE],

How would I remove the first 5 characters when the field contain any of the following. Basically I want to strip out the characters between < and > and including <> as well. The returned field's length can vary.

<!01>AMSSSS
<!02>SSS
<!03>CMSS
<!04>DMSS
<!05>EMSDDDDD etc...

This gives me just the first characters after the > but I don't know how to get all characters after >

SUBSTRING(Products_Joined.ProductName, 6,1) AS [stripHTML-TITLE],

Was going to use Replace function for all the possible prefixes but that can get rather messy.

Upvotes: 3

Views: 5786

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use STUFF. 

select stuff('<!01>AMSSSS', 1, 5, '')

http://msdn.microsoft.com/en-us/library/ms188043.aspx

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

SUBSTRING(Products_Joined.ProductName, 6,LEN(Products_Joined.ProductName))

Upvotes: 2

Related Questions