asked on

SQL Server Express Expression

I'm using SQL Server 2012 Express and I'm wanting to create a query that uses an expression. I actually built it in MS Access but need to convert it to SQL Server. I have a description field from a table that I'm wanting to trim text. Here is my expression that I'm having a hard time converting. I believe that TRIM doesn't exist in SQL Server and that you have to LTRIM and RTRIM.

Trim(Left(Replace(Trim(Right(Replace([tblorderdetail]![description],",",REPT(" ",255)),255)),"-",REPT(" ",255)),255))

This expression takes the following text example and gives the result of what I want:

Example #1: Men's Shirt, 100% Cotton, Dark Navy Body, White Stitching - XL
Result: Dark Navy Body, White Stitching

Example #2: Men's Shirt, 100% Polyester, White Body - LG
Result: White Body

So in theory, I need to take the second comma and display all the text before the dash and all the text after the second comma.

Shaun Kline

Are you looking for something like this:

declare @Text varchar(100)

SET @Text = 'Men''s Shirt, 100% Cotton, Dark Navy Body, White Stitching - XL'

print LTRIM(RTRIM(substring(@text, charindex(',', @text, charindex(',', @text) + 1) + 1, charindex('-', @text) - 1 - charindex(',', @text, charindex(',', @text) + 1))))

SET @Text = 'Men''s Shirt, 100% Polyester, White Body - LG'

print LTRIM(RTRIM(substring(@text, charindex(',', @text, charindex(',', @text) + 1) + 1, charindex('-', @text) - 1 - charindex(',', @text, charindex(',', @text) + 1))))

Open in new window

ASKER CERTIFIED SOLUTION