Guidance on best string manipulation technique (image included)

Question

Guidance on best string manipulation technique (image included)

Viewing 3 posts - 16 through 17 (of 17 total)

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

MPF Old Hand Points: 340 More actions · Answer 1

Hi Wayne,

I completely appreciate what you said and that if i don't understand what it's doing then don't use it.

Your write-up and description however is really very helpful indeed. It had spurred me on to take each section of your code apart this morning and build it back up piece by piece using just 1 record to test it. You're right, it's quite simple once you understand, but no less genius in construction, i'm still mightily impressed with your solution and it's not something i could have come up with!

The Cross Apply function is something i've seen plenty of times before but have never really quite understood it...until now 🙂 What a useful tool, i'll be using that in the future myself i'm sure.

I am even more thankful for your time and effort that before now Wayne. I still cant believe how much help you can get from a stranger over the internet! Great work!!

Cheers!

WayneS SSC Guru Points: 95460 More actions · Answer 2

MPF (7/23/2015)
Hi Wayne,
I completely appreciate what you said and that if i don't understand what it's doing then don't use it.
Your write-up and description however is really very helpful indeed. It had spurred me on to take each section of your code apart this morning and build it back up piece by piece using just 1 record to test it. You're right, it's quite simple once you understand, but no less genius in construction, i'm still mightily impressed with your solution and it's not something i could have come up with!
The Cross Apply function is something i've seen plenty of times before but have never really quite understood it...until now 🙂 What a useful tool, i'll be using that in the future myself i'm sure.
I am even more thankful for your time and effort that before now Wayne. I still cant believe how much help you can get from a stranger over the internet! Great work!!
Cheers!

I'm glad you tore it apart to understand it. Now that you've seen how it works, I'm sure you'll find other places for using this.

In my signature are links to two articles on using the APPLY operator - those would be really good to read to understand what they are doing.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 3

Here's yet another version...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp;

SELECT 'S3925' AS partnum, 'MDSHT 3000 x 1500 x 6mm (7.5mmO/A HEIGHT) MS' AS partdescription INTO #temp;

SELECT

t.partnum,

MAX(CASE WHEN sc.ItemNumber = 2 THEN sc.Item END ) AS [Length],

MAX(CASE WHEN sc.ItemNumber = 4 THEN sc.Item END ) AS [Width],

REPLACE(MAX(CASE WHEN sc.ItemNumber = 6 THEN sc.Item END ), 'mm', '') AS [Thickness],

RIGHT(MAX(t.partdescription), SUM(CASE WHEN sc.ItemNumber >= 7 THEN LEN(sc.Item) END) +1) AS Grade

FROM

#temp t

CROSS APPLY dbo.SplitCSVToTable8K(t.partdescription, ' ') sc

GROUP BY

t.partnum

Note that SplitCSVToTable8K is a renamed version of Jeff's function.