May 5, 2011 at 2:43 pm
I have 1000's of rows of data in column A and B of table 1 in a database A
example data
ProductID --- ProductName
1 --- 0.9% NACL 250ML IV BAG
2 --- METHOTREXATE 50MG BU
3 --- 0.9% NACL 100ML VIAL
4 --- HEPARIN LF 100 UNITS/1ML 5ML FLUSH
5 --- DEXAMETHASONE 4MG/ML
6 --- 0.45% NACL 250ML IV Bag
7 --- 0.45% NACL 500ML IV BAG
8 --- 0.9% NACL 1000ML IV BAG
9 --- EPINEPHERINE 1ML AMP
10 --- LEUCOVORIN 100MG VIAL
11 --- LOPERAMIDE 2MG CAPS
i need to parse this into multiple tables in a new database maybe as a stored procedure
for example "0.45% NACL 250ML IV Bag" would be
Name "0.45% NACL"
Dose "250"
DoseType "ML"
Unit "IV Bag"
There would be a preexisting table with dosetypes where dosetype "ml" would have a doseID of "11"
the new table would have the doseID instead of the string
the same for unit instead of bag some kind of conditional expression would check UnitTable for bag and place UnitID associated with bag in the new table
I have no idea where to start with this even some pseudo or example code would help
regExpressions or conditional statements
any input would help
May 5, 2011 at 2:48 pm
The elements in yous ColumnA is not following a pattern. This makes it impossible to write a parser for this. Will the number of elements in COlumnA vary (i see the u have put the sample data, what about the original data) ?
May 5, 2011 at 2:52 pm
this is a tough requirement,a nd will undoubtedly require multiple passes of SQLs and lots of code; there is no way you can do this in a simple, easy , single pass of a query statement.
for example, if you do this:
SELECT * From YourTable Where ProductName like '%NACL%'
is EVERY record formatted the same way? if they are, you can split the data for that specific Prooduct into the parts you wanted; if not, you have to further segregate/organize some subselects until the data is common enough for you to parse.
you'll have to do that for every style you can identify...that is not an easy task.
Lowell
May 5, 2011 at 2:58 pm
I am sorry it is actually 2 columns one with an id and one with a string
yes they will vary but alot of them stick to the pattern i mentioned
but i only need to move the data that sticks to the pattern,
the rest i need to place in another table all in itself (deal with it later)
The biggest problem i am thinking of is how to separate the product name there are so many different names i cant just return them alphabetical and extract each one in multiple statements
May 5, 2011 at 6:08 pm
There are couple of solutions in your problem
1. You can loop to each record and split
2. put that split data in temporary table and make sure you preserve the id for that product
3. you can join and do clean ups of your temporary table into the new table
hope this will help
hybrid = coder / dba :alien:
May 5, 2011 at 6:53 pm
kcbeard (5/5/2011)
I have 1000's of rows of data in column A and B of table 1 in a database Aexample data
ProductID --- ProductName
1 --- 0.9% NACL 250ML IV BAG
2 --- METHOTREXATE 50MG BU
3 --- 0.9% NACL 100ML VIAL
4 --- HEPARIN LF 100 UNITS/1ML 5ML FLUSH
5 --- DEXAMETHASONE 4MG/ML
6 --- 0.45% NACL 250ML IV Bag
7 --- 0.45% NACL 500ML IV BAG
8 --- 0.9% NACL 1000ML IV BAG
9 --- EPINEPHERINE 1ML AMP
10 --- LEUCOVORIN 100MG VIAL
11 --- LOPERAMIDE 2MG CAPS
i need to parse this into multiple tables in a new database maybe as a stored procedure
for example "0.45% NACL 250ML IV Bag" would be
Name "0.45% NACL"
Dose "250"
DoseType "ML"
Unit "IV Bag"
There would be a preexisting table with dosetypes where dosetype "ml" would have a doseID of "11"
the new table would have the doseID instead of the string
the same for unit instead of bag some kind of conditional expression would check UnitTable for bag and place UnitID associated with bag in the new table
I have no idea where to start with this even some pseudo or example code would help
regExpressions or conditional statements
any input would help
If anyone is ever bored, go lookup regular expressions on wiki and fry your brain a bit. I'll learn not to do that at the end of a day... 🙂
Anyway, what you're looking for here is to be able to parse this down, which means you need to be able to determine what you want to do with your parsing. If you can't write it down, you can't code it out.
Let's look at two examples:
1 --- 0.9% NACL 250ML IV BAG
2 --- METHOTREXATE 50MG BU
Your name is one or two 'words' long. If it starts with an integer, it's 2 words. If it starts with a text char, it's one word. This tells you how many spaces along to go to find the end of the name. You could alternately say 'any word after the first word that has a numeric character ends the name and everything previous is the name'.
Your Dose starts at the first numeric found after the name. It is all the numeric characters that begin that word.
Your DoseType is the remaining portion of the word that began with your dosage and is the remaining characters.
Finally, Unit is anything that's left.
The use of PATINDEX here with ranges of characters (most likely [0-9] and ^[0-9]) will help you locate the positions you need. CHARINDEX on ' ' will help you locate word beginning and ending points.
If you can load this up into a structure like you'll find in the first link in my signature, we can probably help you further with the coding itself. Be aware, though, this is not going to look very pretty.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 5, 2011 at 7:01 pm
Another solution as i believe there is no split function in sql
what i mean was use substring, patindex as mentioned etc.
example
declare @string varchar(max)
set @string = '0.9% NACL 250ML IV BAG'
select left(@string, charindex('%', @string))
select right(@string, len(@string) - charindex('%', @string))
you need to iterate to your records and add these data in a temporary table
hybrid = coder / dba :alien:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply