Solved

SQL Server Express Expression

Posted on 2013-11-08
4
379 Views
Last Modified: 2013-11-21
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.
0
Comment
Question by:Southern_Gentleman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39634457
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

0
 
LVL 32

Accepted Solution

by:
bhess1 earned 400 total points
ID: 39634701
Pretty straight forward, really:

SELECT LTRIM(RTRIM(SUBSTRING(SUBSTRING(tblOrderDetail.description, 1, LEN(tblOrderDetail.description)-CHARINDEX('-', REVERSE(tblOrderDetail.description))), CHARINDEX(',', STUFF(tblOrderDetail.description, CHARINDEX(',', tblOrderDetail.description), 1, '.')+1), LEN(tblOrderDetail.description))))


What?  That's unclear? :-)  Let me break it down.

SUBSTRING(tblOrderDetail.description, 1, LEN(tblOrderDetail.description)-CHARINDEX('-', REVERSE(tblOrderDetail.description)))

This makes the assumption that someone could, possibly, have another hyphen in the text, so that you might have to process this someday:

Men's Shirt, 100% Cotton, Off-White Body, Red Stitching - XL

Thus, you need to make certain that you are finding the LAST hyphen.  To do so, you use

CHARINDEX('-', REVERSE(tblOrderDetail.description))

This looks for the position of the first hyphen when the sequence of characters in tblOrderDetail.Description is reversed, and returns its position - 4, in our example above.

SUBSTRING(tblOrderDetail.description, 1, LEN(tblOrderDetail.description)- {4})

Get everything up to, but not including, the hyphen (determined by the first part of this section to be four.) This gives us our working string when we go to find the second comma in the description.

SUBSTRING({'Men's Shirt, 100% Cotton, Off-White Body, Red Stitching '}, CHARINDEX(',', STUFF(tblOrderDetail.description, CHARINDEX(',', tblOrderDetail.description), 1, '.')+1), LEN(tblOrderDetail.description))

In this, we take the original string, and replace the first comma with a period:

STUFF(tblOrderDetail.description, CHARINDEX(',', tblOrderDetail.description), 1, '.')

Then find the position immediately after the first remaining comma:

CHARINDEX(',', ~~~) + 1

And take a SUBSTRING of the result from step 1, from the position identified to the end of the string:

SUBSTRING('Men's Shirt, 100% Cotton, Off-White Body, Red Stitching ', ~~~, LEN(tblOrderDetail.description))

Resulting in this string - ' Off-White Body, Red Stitching ' - which we LTRIM and RTRIM to remove the residual space.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 39636453
I like using cross apply for such things as you can do something in a cross apply and then re-use it by reference to its column alias; and an item in one cross apply can be referenced this way in following cross applies as well. In short; it's neater.

Before the query though, I added a couple of tests, and provided 2 output options:
|           OPTION1 |           OPTION2 |                                                    DESCRIPTION |                                                       REVDESCR | POS1 | POS2 |
|-------------------|-------------------|----------------------------------------------------------------|----------------------------------------------------------------|------|------|
|  White Stitching  |  White Stitching  | Men's Shirt, 100% Cotton, Dark Navy Body, White Stitching - XL | LX - gnihctitS etihW ,ydoB yvaN kraD ,nottoC %001 ,trihS s'neM |   22 |    4 |
|      none applies |            (null) |                                                   none applies |                                                   seilppa enon |    0 |    0 |
|   but has, commas |            (null) |                                                but has, commas |                                                sammoc ,sah tub |    8 |    0 |
|    Red Stitching  |    Red Stitching  |                                             Red Stitching - XL |                                             LX - gnihctitS deR |    0 |    4 |
|       White Body  |       White Body  |                   Men's Shirt, 100% Polyester, White Body - LG |                   GL - ydoB etihW ,retseyloP %001 ,trihS s'neM |   17 |    4 |

Open in new window

The result is produced by the following query (I include * in the output so you may see the cross apply calculations):
SELECT
        CASE WHEN pos1 > 0 AND pos2 > 0 THEN reverse(substring(revdescr,pos2+1,pos1-pos2-1))
             WHEN pos2 > 0 THEN reverse(substring(revdescr,pos2+1,len(description)))
             ELSE description
        END as option1
      
      , CASE WHEN pos1 > 0 AND pos2 > 0 THEN reverse(substring(revdescr,pos2+1,pos1-pos2-1))
             WHEN pos2 > 0 THEN reverse(substring(revdescr,pos2+1,len(description)))
        END as option2

      , *
FROM tblorderdetail
CROSS apply ( SELECT reverse(description) ) AS ca1 (revdescr)
CROSS apply ( SELECT charindex(',',revdescr), charindex('-',revdescr) ) AS ca2 (pos1, pos2)
;

Open in new window

The first cross apply reverse description [revdescr]
The second cross apply gets position of first comma and first dash in [revdescr] as [pos1] and [pos2]
Then in the select clause those column aliases as used in case expressions to extract the wanted string
(Only one of those case expressions is really needed of course)
0
 

Author Closing Comment

by:Southern_Gentleman
ID: 39667438
Awesome guys.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question