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

  • 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

  • search for the DelimitedSplit8K function, and understand the article.

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

    /*--Results

    jmaPartShortDescription Item Item Item Item Item Item

    5.625X11.25-2.875x8.5 5.625X11.25 2.875x8.5 5.625 11.25 2.875 8.5

    5.625X35.25-2.875x32.5 5.625X35.25 2.875x32.5 5.625 35.25 2.875 32.5

    22.5X9.75-9.5x19 22.5X9.75 9.5x19 22.5 9.75 9.5 19

    28.5X9.75-9.5x25 28.5X9.75 9.5x25 28.5 9.75 9.5 25

    40.125X9.75-9.5x36.625 40.125X9.75 9.5x36.625 40.125 9.75 9.5 36.625

    22.5X11.25-11x19 22.5X11.25 11x19 22.5 11.25 11 19

    22.5X11.25-11x19 22.5X11.25 11x19 22.5 11.25 11 19

    34.125X11.25-11x30.625 34.125X11.25 11x30.625 34.125 11.25 11 30.625

    22.5X12.75-12.5x19 22.5X12.75 12.5x19 22.5 12.75 12.5 19

    34.125X12.75-12.5x30.625 34.125X12.75 12.5x30.625 34.125 12.75 12.5 30.625

    24.375X8.8125-8.5625x20.375 24.375X8.8125 8.5625x20.375 24.375 8.8125 8.5625 20.375

    18X12.25-12x14 18X12.25 12x14 18 12.25 12 14

    5.625X8.25-2.875x5.5 5.625X8.25 2.875x5.5 5.625 8.25 2.875 5.5

    5.625X12.75-2.875x10 5.625X12.75 2.875x10 5.625 12.75 2.875 10

    5.625X14.25-2.875x11.5 5.625X14.25 2.875x11.5 5.625 14.25 2.875 11.5

    23.75X20.25-15.5x19 23.75X20.25 15.5x19 23.75 20.25 15.5 19

    5.875X10.75-3x10.5 5.875X10.75 3x10.5 5.875 10.75 3 10.5

    12.5625X10.75-9.6875x10.5 12.5625X10.75 9.6875x10.5 12.5625 10.75 9.6875 10.5

    11.3125X10.75-8.4375x10.5 11.3125X10.75 8.4375x10.5 11.3125 10.75 8.4375 10.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 '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!

  • 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 '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
  • 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 '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/

  • 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)

  • 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 '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

  • 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

    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

    25x11.5

    .5x25

    .5x25

    x20.5

    25x19

    x26.5

    25x19

    24.875

  • 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
  • When I do I get nothing.

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

  • 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 '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 '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
  • thanks I will use the reverse and see how that works. Thanks again

  • 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'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/

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

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