How do use SQL to move a portion of the string in a field to its own new field (all records at once)?
My problem is that I have a huge table in which I combined what should have been 2 fields into 1 field. I need to separate them. Here is an example of the format of the problem field. This would be 4 records of just the problem field.
Blue
my favorite color
Apple
my favorite fruit
Saturday
my favorite day of the week
Baseball
my favorite sport
What I want to move to a new field is within the tag. This is true for all records. For instance, for the first record I would want to move the string “my favorite color” to a new field so that “Blue
” would be in its own field and “my favorite color” would be in its own field. Of course, I would then want to get rid of all the html junk after “Blue”, but thats another issue. Thanks in advance for your help.
For SQL Server:
select LEFT(x,charindex(’‘,x)-1) x,
SUBSTRING(x,charindex(’‘,x), len(x)) y
from
(
select
‘Blue
my favorite color‘ as x
union all select ‘Apple
my favorite fruit‘
union all select ‘Saturday
my favorite day of the week‘
union all select ‘Baseball
my favorite sport‘
) T
|
|
Bradley® 120 – Pk. Flavored Bisquettes $44.99 Prepare luscious smoked food right at home… Alder, Apple, Cherry, Hickory, Maple, Mesquite, Oak, Pecan, Special Blend. Order yours today! AVAILABLE SEPARATELY: Bradley 4-Rack Digital Smoker; Bradley 6-Rack Digital Smoker; Bradley Propane Smoker; Bradley Original Black Smoker; Bradley Stainless Steel Smoker; Bradley 60-Pk. Flavored Bisquettes; Bradley 4 Jerky Racks; Bradley 4 Jerky Racks, Teflon … |
|
|
Purple Silicon Skin Case with Armband for iPod nano $8.07 iPod nano case has a purple finishCase is compatible with iPod nano onlyiPod accessory has a soft silicon skin caseVinyl windows shields screenReinforced bottom opening for easy access to dock ports and headphone jack |