?
Solved

SQL Server Express Expression

Posted on 2013-11-08
4
Medium Priority
?
385 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:
Brendt Hess earned 1600 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

764 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