Solved

SQL Server Express Expression

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 the fundamental information of how to create a table.

862 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now