Brian_Sutherland
asked on
Load Table with substrings from field in other table
I have attached code that shows how I can get the strings between the '-' character and it works fine. Now, what I really need to do is select a field from table1 with the value like 'MENS-AVG-R-08FL-US' and load the pieces of it into another table.
Example: From field 1 in Table 1 with a value of 'MENS-AVG-R-08FL-US',
load Table2 like this:
field1 = 'MENS'
field2 = 'AVG'
field3='R'
field4='08FL'
field5='US'
Don't know the best way to get it done.
Example: From field 1 in Table 1 with a value of 'MENS-AVG-R-08FL-US',
load Table2 like this:
field1 = 'MENS'
field2 = 'AVG'
field3='R'
field4='08FL'
field5='US'
Don't know the best way to get it done.
DECLARE @index int
SET @index = -1
DECLARE @value varchar(100)
Declare @section1 int
declare @section2 int
declare @section3 int
declare @section4 int
declare @count int
SET @count = 0
WHILE @index > 0 OR @index = -1
BEGIN
SELECT @index = CHARINDEX('-','MENS-AVG-R-08FL-US' , @index+1)
select @count = @count+1
if @count = 1
set @section1 = @index
if @count = 2
set @section2 = @index
if @count = 3
set @section3 = @index
if @count = 4
set @section4 = @index
--Insert Into #Temp1(
END
SELECT SUBSTRING('MENS-AVG-R-08FL-US', @section4+1, len('MENS-AVG-R-08FL-US')),@section1,@section2,@section3,@section4
ASKER
This is above my skill level and I could use a little help. Attached is a temp table that I create that will pull the distinct vaulues like 'MENS-AVG-R-08FL-US' . What I was thinking is that I could then use your solution by pulling the EVENTID into the temp table as this will be much smaller than going through a million records. First, does this make sense and if it does then how do I set up the code. I am going to try in the meantime to do it, but any suggestions are appreciated.
Keep in mind that the values are not always the same length, for instance I could have a value where the second value (AVG) could have a length of 4 (AVGS).
Keep in mind that the values are not always the same length, for instance I could have a value where the second value (AVG) could have a length of 4 (AVGS).
CREATE TABLE #WRK_MODELSETS
(EVENTID VARCHAR(30),S_ID VARCHAR(4), CAT VARCHAR(4),STORE VARCHAR(4),
PRODUCT_ID VARCHAR(8),COUNTRY VARCHAR(2) )
INSERT INTO #WRK_MODELSETS (EVENTID) SELECT DISTINCT EVENTID From dbo.table1
where eventtype = 'B&M'
and len(productid) = 8
and eventid not like '%05%'
and eventid not like '%06SP%'
Hi,
Are there always 5 fields?
Cheers
David
Are there always 5 fields?
Cheers
David
ASKER
Yes, there is always five fields.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect - I now understand how to do it and it was much faster than the cursor I had set up to do it.
https://www.experts-exchange.com/questions/23068609/Passing-the-value-for-an-IN-clause-to-a-stored-procedure.html
This solution is about a comma separated string. You basically have the same thing, but using hyphens instead of commas.
The solution uses a function which returns a table from the input string. In your case the table can be the source to an insert.
HTH
David