Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Express Expression

Posted on 2013-11-08
4
Medium Priority
?
397 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 27

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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