Solved

SQL Server Express Expression

Posted on 2013-11-08
4
374 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
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

785 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