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.

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

Open in new window

Brian_SutherlandAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
Hi,

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23068609.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
Brian_SutherlandAuthor Commented:
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).
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%'

Open in new window

David ToddSenior Database AdministratorCommented:
Hi,

Are there always 5 fields?

Cheers
  David
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian_SutherlandAuthor Commented:
Yes, there is always five fields.
David ToddSenior Database AdministratorCommented:
Hi,

I've written this as a select against one row in a temp table.

You should be able to create the join, and create the assignments to update the five columns you want.

Yes the code isn't pretty, but it should preform reasonably well.

HTH
  David



use tempdb
go
 
if object_id( N'tempdb..#t', N'U' ) is not null 
	drop table #t;
	
create table #t 
	(
	t varchar( 20 )
	)
	
insert #t( t )values( 'MENS-AVG-R-08FL-US' )
 
select *
from #t
 
--declare @v varchar( 20 )
--set @v = 'MENS-AVG-R-08FL-US'
--
--select 
--	@v
	
select
	left( t.t, charindex( '-', t.t ) - 1)
	, substring( t.t, charindex( '-', t.t ) + 1, charindex( '-', t.t, charindex( '-', t.t) + 1 ) - charindex( '-', t.t ) - 1 )
	, substring( 
		t.t
		, charindex( '-', t.t, charindex( '-', t.t) + 1 ) + 1
		, charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t) + 1) + 1 ) - charindex( '-', t.t, charindex( '-', t.t) + 1 ) - 1
	)
	, substring(
		t.t
		, charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t) + 1) + 1 ) + 1
		, 
			charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t) + 1) + 1 ) + 1 )
			- charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t) + 1) + 1 ) - 1
	)
	, right( t.t, len( t.t ) - charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t) + 1) + 1 ) + 1 ))
from #t t
 
select 	
	charindex( '-', t.t )
	, charindex( '-', t.t, charindex( '-', t.t) + 1)
	, charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t) + 1) + 1 )
	, charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t, charindex( '-', t.t) + 1) + 1 ) + 1 )
from #t t
	
	

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian_SutherlandAuthor Commented:
Perfect - I now understand how to do it and it was much faster than the cursor I had set up to do it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.