I have a field that has data like this and I need to pull out what is between the "-" and "x" 5.625X11.25-2.875x8.5 would be 2.875

Question

Post reply

I have a field that has data like this and I need to pull out what is between the "-" and "x" 5.625X11.25-2.875x8.5 would be 2.875

kat35601

SSC Eights!

Points: 825
More actions
February 17, 2015 at 9:06 am

#301263

How can I do this?
I have a field that has data like this and I need to pull out what is between the "-" and "x" 5.625X11.25-2.875x8.5 would be 2.875
the data looks like this
jmaPartShortDescription
5.625X11.25-2.875x8.5
5.625X35.25-2.875x32.5
22.5X9.75-9.5x19
28.5X9.75-9.5x25
40.125X9.75-9.5x36.625
22.5X11.25-11x19
22.5X11.25-11x19
34.125X11.25-11x30.625
22.5X12.75-12.5x19
34.125X12.75-12.5x30.625
24.375X8.8125-8.5625x20.375
18X12.25-12x14
5.625X8.25-2.875x5.5
5.625X12.75-2.875x10
5.625X14.25-2.875x11.5
23.75X20.25-15.5x19
5.875X10.75-3x10.5
12.5625X10.75-9.6875x10.5
11.3125X10.75-8.4375x10.5

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

search for the DelimitedSplit8K function, and understand the article.

then it's just a matter of chopping up the strings:

/*--Results

jmaPartShortDescriptionItemItemItemItemItemItem

5.625X11.25-2.875x8.5 5.625X11.252.875x8.5 5.62511.252.8758.5

5.625X35.25-2.875x32.5 5.625X35.252.875x32.5 5.62535.252.87532.5

22.5X9.75-9.5x19 22.5X9.759.5x19 22.59.759.519

28.5X9.75-9.5x25 28.5X9.759.5x25 28.59.759.525

40.125X9.75-9.5x36.625 40.125X9.759.5x36.625 40.1259.759.536.625

22.5X11.25-11x19 22.5X11.2511x19 22.511.251119

34.125X11.25-11x30.625 34.125X11.2511x30.625 34.12511.251130.625

22.5X12.75-12.5x19 22.5X12.7512.5x19 22.512.7512.519

34.125X12.75-12.5x30.625 34.125X12.7512.5x30.625 34.12512.7512.530.625

24.375X8.8125-8.5625x20.375 24.375X8.81258.5625x20.375 24.3758.81258.562520.375

18X12.25-12x14 18X12.2512x14 1812.251214

5.625X8.25-2.875x5.5 5.625X8.252.875x5.5 5.6258.252.8755.5

5.625X12.75-2.875x10 5.625X12.752.875x10 5.62512.752.87510

5.625X14.25-2.875x11.5 5.625X14.252.875x11.5 5.62514.252.87511.5

23.75X20.25-15.5x19 23.75X20.2515.5x19 23.7520.2515.519

5.875X10.75-3x10.5 5.875X10.753x10.5 5.87510.75310.5

12.5625X10.75-9.6875x10.5 12.5625X10.759.6875x10.5 12.562510.759.687510.5

11.3125X10.75-8.4375x10.511.3125X10.758.4375x10.511.312510.758.437510.5

*/

;WITH MyCTE([jmaPartShortDescription])

AS

(

SELECT '5.625X11.25-2.875x8.5 ' UNION ALL

SELECT '5.625X35.25-2.875x32.5 ' UNION ALL

SELECT '22.5X9.75-9.5x19 ' UNION ALL

SELECT '28.5X9.75-9.5x25 ' UNION ALL

SELECT '40.125X9.75-9.5x36.625 ' UNION ALL

SELECT '22.5X11.25-11x19 ' UNION ALL

SELECT '34.125X11.25-11x30.625 ' UNION ALL

SELECT '22.5X12.75-12.5x19 ' UNION ALL

SELECT '34.125X12.75-12.5x30.625 ' UNION ALL

SELECT '24.375X8.8125-8.5625x20.375 ' UNION ALL

SELECT '18X12.25-12x14 ' UNION ALL

SELECT '5.625X8.25-2.875x5.5 ' UNION ALL

SELECT '5.625X12.75-2.875x10 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '23.75X20.25-15.5x19 ' UNION ALL

SELECT '5.875X10.75-3x10.5 ' UNION ALL

SELECT '12.5625X10.75-9.6875x10.5 ' UNION ALL

SELECT '11.3125X10.75-8.4375x10.5'

)

SELECT * FROM MyCTE

OUTER APPLY (SELECT Item FROM dbo.DelimitedSplit8k(jmaPartShortDescription,'-') WHERE ItemNumber = 1) T1

OUTER APPLY (SELECT Item FROM dbo.DelimitedSplit8k(jmaPartShortDescription,'-') WHERE ItemNumber = 2) T2

OUTER APPLY (SELECT Item FROM dbo.DelimitedSplit8k(t1.Item,'X') WHERE ItemNumber = 1) T3

OUTER APPLY (SELECT Item FROM dbo.DelimitedSplit8k(t1.Item,'X') WHERE ItemNumber = 2) T4

OUTER APPLY (SELECT Item FROM dbo.DelimitedSplit8k(t2.Item,'X') WHERE ItemNumber = 1) T5

OUTER APPLY (SELECT Item FROM dbo.DelimitedSplit8k(t2.Item,'X') WHERE ItemNumber = 2) T6

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Lowell's solution is very complete, but if you only need one part of the string, it seems as an overkill to me.

WITH MyCTE([jmaPartShortDescription])

AS

(

SELECT '5.625X11.25-2.875x8.5 ' UNION ALL

SELECT '5.625X35.25-2.875x32.5 ' UNION ALL

SELECT '22.5X9.75-9.5x19 ' UNION ALL

SELECT '28.5X9.75-9.5x25 ' UNION ALL

SELECT '40.125X9.75-9.5x36.625 ' UNION ALL

SELECT '22.5X11.25-11x19 ' UNION ALL

SELECT '34.125X11.25-11x30.625 ' UNION ALL

SELECT '22.5X12.75-12.5x19 ' UNION ALL

SELECT '34.125X12.75-12.5x30.625 ' UNION ALL

SELECT '24.375X8.8125-8.5625x20.375 ' UNION ALL

SELECT '18X12.25-12x14 ' UNION ALL

SELECT '5.625X8.25-2.875x5.5 ' UNION ALL

SELECT '5.625X12.75-2.875x10 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '23.75X20.25-15.5x19 ' UNION ALL

SELECT '5.875X10.75-3x10.5 ' UNION ALL

SELECT '12.5625X10.75-9.6875x10.5 ' UNION ALL

SELECT '11.3125X10.75-8.4375x10.5'

)

SELECT *

FROM MyCTE

CROSS APPLY (SELECT STUFF( [jmaPartShortDescription], 1, CHARINDEX('-', [jmaPartShortDescription]), '')) Part2(string)

CROSS APPLY (SELECT LEFT(Part2.string, CHARINDEX('x', Part2.string + 'x') - 1)) Final(value);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Or if your data is consistent in the formatting you can do this with some string manipulation.

Using Lowell's excellent setup (thanks):

;WITH MyCTE([jmaPartShortDescription])

AS

(

SELECT '5.625X11.25-2.875x8.5 ' UNION ALL

SELECT '5.625X35.25-2.875x32.5 ' UNION ALL

SELECT '22.5X9.75-9.5x19 ' UNION ALL

SELECT '28.5X9.75-9.5x25 ' UNION ALL

SELECT '40.125X9.75-9.5x36.625 ' UNION ALL

SELECT '22.5X11.25-11x19 ' UNION ALL

SELECT '34.125X11.25-11x30.625 ' UNION ALL

SELECT '22.5X12.75-12.5x19 ' UNION ALL

SELECT '34.125X12.75-12.5x30.625 ' UNION ALL

SELECT '24.375X8.8125-8.5625x20.375 ' UNION ALL

SELECT '18X12.25-12x14 ' UNION ALL

SELECT '5.625X8.25-2.875x5.5 ' UNION ALL

SELECT '5.625X12.75-2.875x10 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '23.75X20.25-15.5x19 ' UNION ALL

SELECT '5.875X10.75-3x10.5 ' UNION ALL

SELECT '12.5625X10.75-9.6875x10.5 ' UNION ALL

SELECT '11.3125X10.75-8.4375x10.5'

)

SELECT *

, LEFT(SUBSTRING(jmaPartShortDescription, CHARINDEX('-', jmaPartShortDescription) + 1, LEN(jmaPartShortDescription)), charindex('x', SUBSTRING(jmaPartShortDescription, CHARINDEX('-', jmaPartShortDescription) + 1, LEN(jmaPartShortDescription))) - 1)

FROM MyCTE

--EDIT--

Didn't see Luis' post since I was writing mine. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kat35601 SSC Eights! Points: 825 More actions · Answer 4

Thank you very much

This worked great

LEFT(SUBSTRING(jmaPartShortDescription, CHARINDEX('-', jmaPartShortDescription) + 1, LEN(jmaPartShortDescription)), charindex('x', SUBSTRING(jmaPartShortDescription, CHARINDEX('-', jmaPartShortDescription) + 1, LEN(jmaPartShortDescription))) - 1)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

A perfect case for using a pattern-based string splitter:

;WITH MyCTE([jmaPartShortDescription])

AS

(

SELECT '5.625X11.25-2.875x8.5 ' UNION ALL

SELECT '5.625X35.25-2.875x32.5 ' UNION ALL

SELECT '22.5X9.75-9.5x19 ' UNION ALL

SELECT '28.5X9.75-9.5x25 ' UNION ALL

SELECT '40.125X9.75-9.5x36.625 ' UNION ALL

SELECT '22.5X11.25-11x19 ' UNION ALL

SELECT '34.125X11.25-11x30.625 ' UNION ALL

SELECT '22.5X12.75-12.5x19 ' UNION ALL

SELECT '34.125X12.75-12.5x30.625 ' UNION ALL

SELECT '24.375X8.8125-8.5625x20.375 ' UNION ALL

SELECT '18X12.25-12x14 ' UNION ALL

SELECT '5.625X8.25-2.875x5.5 ' UNION ALL

SELECT '5.625X12.75-2.875x10 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '23.75X20.25-15.5x19 ' UNION ALL

SELECT '5.875X10.75-3x10.5 ' UNION ALL

SELECT '12.5625X10.75-9.6875x10.5 ' UNION ALL

SELECT '11.3125X10.75-8.4375x10.5'

)

SELECT *

FROM MyCTE

CROSS APPLY dbo.PatternSplitCM([jmaPartShortDescription], '[X-]')

WHERE ItemNumber = 5;

I've chosen the fifth element delimited by x and hyphen (you could choose any).

See the link in my signature to splitting strings based on patterns to find the PatternSplitCM function.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

kat35601 SSC Eights! Points: 825 More actions · Answer 6

I have another question on this subject. I need to get everything from the right of the little x and I get that for the most part with this statement but for some of them I get the x included and on some of them I get a lot more. What am I doing wrong????

Right(rtrim(jmaPartShortDescription),CHARINDEX('x', jmaPartShortDescription)-1) AS length1

17.375X14.5625-9.8125x12.625

35.375X14.5625-9.8125x30.625

17.375X17.5625-12.8125x12.625

5.625X14.25-2.875x11.5

23.75X14.25-9.5x19

5.625X29.25-2.875x26.5

23.75X14.25-9.5x19

5.625X14.25-2.875x11.5

11.0625X14.25-8.3125x11.5

12.3125X14.25-9.5625x11.5

29.75X10.25-5.5x25

5.625X23.25-2.875x20.5

23.75X11.5625-6.8125x19

5.625X29.25-2.875x26.5

23.75X14.5625-9.8125x19

29.875X19.375-19.125x24.875

I end up with this

12.625

30.625

12.625

x11.5

.5x19

x26.5

.5x19

x11.5

25x11.5

.5x25

x20.5

25x19

x26.5

25x19

24.875

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Drop the RTRIM().

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kat35601 SSC Eights! Points: 825 More actions · Answer 8

When I do I get nothing.

Right(jmaPartShortDescription,CHARINDEX('x', jmaPartShortDescription)-1) AS length1

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

My bad, I didn't review the formula correctly.

The problem is that you're counting characters before the first X and you're using that length to chop the last part which isn't always the same length.

An easy option would be to use REVERSE(), but that's an expensive function in terms of performance.

SELECT Right(jmaPartShortDescription,CHARINDEX('x', REVERSE( jmaPartShortDescription))-1) AS length1

If you're splitting the whole string, my advice is to use a splitter.

WITH MyCTE([jmaPartShortDescription])

AS

(

SELECT '17.375X14.5625-9.8125x12.625 ' UNION ALL

SELECT '35.375X14.5625-9.8125x30.625 ' UNION ALL

SELECT '17.375X17.5625-12.8125x12.625 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '23.75X14.25-9.5x19 ' UNION ALL

SELECT '5.625X29.25-2.875x26.5 ' UNION ALL

SELECT '23.75X14.25-9.5x19 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '11.0625X14.25-8.3125x11.5 ' UNION ALL

SELECT '12.3125X14.25-9.5625x11.5 ' UNION ALL

SELECT '29.75X10.25-5.5x25 ' UNION ALL

SELECT '5.625X23.25-2.875x20.5 ' UNION ALL

SELECT '23.75X11.5625-6.8125x19 ' UNION ALL

SELECT '5.625X29.25-2.875x26.5 ' UNION ALL

SELECT '23.75X14.5625-9.8125x19 ' UNION ALL

SELECT '29.875X19.375-19.125x24.875 '

)

SELECT jmaPartShortDescription,

MAX(CASE WHEN ItemNumber = 1 THEN Item END),

MAX(CASE WHEN ItemNumber = 3 THEN Item END),

MAX(CASE WHEN ItemNumber = 5 THEN Item END),

MAX(CASE WHEN ItemNumber = 7 THEN Item END)

FROM MyCTE

CROSS APPLY dbo.PatternSplitCM([jmaPartShortDescription], '[X-]') s

WHERE ItemNumber IN(1,3,5,7)

GROUP BY jmaPartShortDescription;

WITH MyCTE([jmaPartShortDescription])

AS

(

SELECT '17.375X14.5625-9.8125x12.625 ' UNION ALL

SELECT '35.375X14.5625-9.8125x30.625 ' UNION ALL

SELECT '17.375X17.5625-12.8125x12.625 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '23.75X14.25-9.5x19 ' UNION ALL

SELECT '5.625X29.25-2.875x26.5 ' UNION ALL

SELECT '23.75X14.25-9.5x19 ' UNION ALL

SELECT '5.625X14.25-2.875x11.5 ' UNION ALL

SELECT '11.0625X14.25-8.3125x11.5 ' UNION ALL

SELECT '12.3125X14.25-9.5625x11.5 ' UNION ALL

SELECT '29.75X10.25-5.5x25 ' UNION ALL

SELECT '5.625X23.25-2.875x20.5 ' UNION ALL

SELECT '23.75X11.5625-6.8125x19 ' UNION ALL

SELECT '5.625X29.25-2.875x26.5 ' UNION ALL

SELECT '23.75X14.5625-9.8125x19 ' UNION ALL

SELECT '29.875X19.375-19.125x24.875 '

)

SELECT jmaPartShortDescription,

MAX(CASE WHEN ItemNumber = 1 THEN Item END),

MAX(CASE WHEN ItemNumber = 2 THEN Item END),

MAX(CASE WHEN ItemNumber = 3 THEN Item END),

MAX(CASE WHEN ItemNumber = 4 THEN Item END)

FROM MyCTE

CROSS APPLY (SELECT REPLACE( [jmaPartShortDescription], 'X', '-') replaced) r

CROSS APPLY dbo.delimitedsplit8k(r.replaced, '-') s

GROUP BY jmaPartShortDescription

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kat35601 SSC Eights! Points: 825 More actions · Answer 10

thanks I will use the reverse and see how that works. Thanks again

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

You can try this:

;WITH CTE (sig)AS

(

SELECT'5.625X11.25-2.875x8.5' UNION ALL

SELECT'5.625X35.25-2.875x32.5' UNION ALL

SELECT'22.5X9.75-9.5x19'UNION ALL

SELECT'28.5X9.75-9.5x25'UNION ALL

SELECT'40.125X9.75-9.5x36.625'UNION ALL

SELECT'22.5X11.25-11x19'UNION ALL

SELECT'34.125X11.25-11x30.625'UNION ALL

SELECT'22.5X12.75-12.5x19'UNION ALL

SELECT'34.125X12.75-12.5x30.625'UNION ALL

SELECT'24.375X8.8125-8.5625x20.375'UNION ALL

SELECT'18X12.25-12x14'UNION ALL

SELECT'5.625X8.25-2.875x5.5'UNION ALL

SELECT'5.625X12.75-2.875x10'UNION ALL

SELECT'5.625X14.25-2.875x11.5'UNION ALL

SELECT'23.75X20.25-15.5x19'UNION ALL

SELECT'5.875X10.75-3x10.5'UNION ALL

SELECT'12.5625X10.75-9.6875x10.5'UNION ALL

SELECT'11.3125X10.75-8.4375x10.5'

)

SELECT

SUBSTRING(sig, CHARINDEX('-',sig,1)+ 1,CHARINDEX('x',sig,CHARINDEX('-',sig,1))- CHARINDEX('-',sig,1)-1)

FROM CTE

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/