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?
 
David ToddSenior DBACommented:
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

0
 
David ToddSenior DBACommented:
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
0
 
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
David ToddSenior DBACommented:
Hi,

Are there always 5 fields?

Cheers
  David
0
 
Brian_SutherlandAuthor Commented:
Yes, there is always five fields.
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.