Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

t-sql parse out data Expand / Collapse
Posted Friday, January 4, 2013 12:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 563, Visits: 697
My goal is to parse out a company name and contact name that is the actual filename of the excel spreadsheet.

I will use this information in a sql server 2008 r2 database to obtain other related information for the company and contact person.
My problem is the file name is manually keyed in my the user who runs the sql server 2008 r2 reports (ssrs) on the standard edition.
Is there away you would suggest that I try to parse out the company and contact name from the file name? There are delimiters between the company and contact names how the delimiters may change.

You assume that I will pass the filename to the t-sql. However I would like to know how you would setup the sql to accomodate the various in delimiters that may appear and the various of where the delimiters would appear at?

Can you show me in code and/or point me to a reference on how to accomplish my goal?
Post #1403060
Posted Friday, January 4, 2013 12:27 PM



Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 14,539, Visits: 38,377
wendy we need some examples, i think;
splitting the name based on some character is easy; PARSENAME or the famous DelimitedSplit8K are two examples that come to mind;
but unless there is a naming convention, ie the first x segments constitute the company name, i don't know what i can offer.

With MySampleData(filename)
SELECT 'wendy-elizabeth-inc-2013-01-04.txt' UNION ALL
SELECT 'lowell_Corporation.txt' UNION ALL
SELECT 'dwainc-LLC-LatestExport.txt'
FROM MySampleData
CROSS APPLY dbo.DelimitedSplit8K(REPLACE(filename,'_','-'),'-') myfn


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!
Post #1403066
Posted Sunday, January 6, 2013 6:33 PM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, February 24, 2016 6:28 AM
Points: 3,977, Visits: 6,431
Apparently Lowell was thinking of me for some reason (not sure why) when he posted so I couldn't help but chime in!

You might want to take a look at the PatternSplitCM FUNCTION described in the 4th article in my signature link. The article contains some examples of parsing out a filename from a string using that function. It might help to get you started.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1403389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse