SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extract a number from a string with like
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/28/2013 :  04:20:34  Show Profile  Reply with Quote
Hello everybody,
The title may not be very clear, but here is my problem:

Table mat
id_mat(int) | name_en(nvarchar)
1 | Steel
2 | Aluminium
3 | Leather
......

Table product
id_product(int) | type_mat(nvarchar)
1000 | 1, 2
1001 | 2
1002 | 1, 3, 10
......

I would to SELECT every id_mat FROM mat who are IN type_mat FROM product.
Of course id_mat 1 won't comes out when it is a 10 or 11....
I tried with an other column type_mat2 as well where the string looks like $1$$3$10$ to do a like '%$...$%'.
Of course when I tried to concat piece of string with my id_mat it didn't work ('S'+id_mat+'S'). Ok I can see your smiling now ;-)
Any help will appreciated.
Thanks

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/28/2013 :  04:38:35  Show Profile  Visit webfred's Homepage  Reply with Quote
The real solution is to have a third table to map products with materials...

type_mat in that form 1,3,10 is a mess!




Too old to Rock'n'Roll too young to die.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/28/2013 :  04:52:06  Show Profile  Visit webfred's Homepage  Reply with Quote
Here is a messy solution for a messy database:


-- declare test tables and insert testdata
declare @mat Table(id_mat int, name_en nvarchar(30))
insert @mat
select 1, 'Steel' union all
select 2, 'Aluminium' union all
select 3, 'Leather'

declare @product Table(id_product int, type_mat nvarchar(30))
insert @product
select 1000, '1, 2' union all
select 1001, '2' union all
select 1002, '1, 3, 10'

-- the solution
select p.*, m.name_en
from @product p
left join @mat m on  ','+replace(p.type_mat,' ','')+',' like '%,'+convert(nvarchar(30),m.id_mat)+',%'



Too old to Rock'n'Roll too young to die.
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/28/2013 :  06:16:23  Show Profile  Reply with Quote
Thanks webfred,
I agree with the messy comment! I didn't build this database but inherited it (and not from my grand mother)... So I have to deal with it, and I am thinking about your solution with a 3rd table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/28/2013 :  06:24:07  Show Profile  Reply with Quote
another way is this



-- declare test tables and insert testdata
declare @mat Table(id_mat int, name_en nvarchar(30))
insert @mat
select 1, 'Steel' union all
select 2, 'Aluminium' union all
select 3, 'Leather'

declare @product Table(id_product int, type_mat nvarchar(30))
insert @product
select 1000, '1, 2' union all
select 1001, '2' union all
select 1002, '1,3,10'

select p.id_product,m.name_en
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval 
            from @product) p
WHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1
ORDER BY p.id_product


output
------------------------------------
id_product	name_en
------------------------------------
1000	        Steel
1000	        Aluminium
1001	        Aluminium
1002	        Leather
1002	        Steel





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/28/2013 :  06:24:32  Show Profile  Reply with Quote
What do you suggest to extract properly the type_mat in a new table?
in a way to have:
id_product | id_mat
1000 | 1
1000 | 2
1001 | 2
.....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/28/2013 :  06:29:57  Show Profile  Reply with Quote
see scenario 4

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/28/2013 :  06:34:54  Show Profile  Reply with Quote
Thank you so much visakh16.
It works perfectly!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/28/2013 :  10:00:12  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/28/2013 :  11:30:58  Show Profile  Reply with Quote
Now I need to select this results into my new table. But keep having error messages.
Could you give the key please?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 02/28/2013 :  11:39:22  Show Profile  Reply with Quote
If the table already exists, and assuming the columns in that table are id_product and name_en do this:
-- declare test tables and insert testdata
declare @mat Table(id_mat int, name_en nvarchar(30))
insert @mat
select 1, 'Steel' union all
select 2, 'Aluminium' union all
select 3, 'Leather'

declare @product Table(id_product int, type_mat nvarchar(30))
insert @product
select 1000, '1, 2' union all
select 1001, '2' union all
select 1002, '1,3,10'

INSERT INTO YourNewTable (id_product, name_en)
select p.id_product,m.name_en
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval 
            from @product) p
WHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1
ORDER BY p.id_product


Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/28/2013 :  11:44:32  Show Profile  Reply with Quote
Sorry, it worked! It was just a conflict with primary keys!
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/28/2013 :  11:46:19  Show Profile  Reply with Quote
Thank you James K.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 02/28/2013 :  12:01:47  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/28/2013 :  12:47:12  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

Sorry, it worked! It was just a conflict with primary keys!


In case conflicts occur use NOT EXISTS based check before you do actual insert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 03/01/2013 :  01:51:01  Show Profile  Reply with Quote
Thanks for the tip visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/01/2013 :  02:01:39  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 03/01/2013 :  03:44:37  Show Profile  Reply with Quote
Now an other/almost same one ;-)
How can use your solution to output only the products with 2 in type_mat
I guess it would be something like (ok now I am turning red):
select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval
from @product p
WHERE xmlval.exist('/Root/Node[.=sql:column("id_mat")]')=1
ORDER BY p.id_product

Thanks in advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/01/2013 :  03:50:49  Show Profile  Reply with Quote
it should be

select distinct p.id_product
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(type_mat,',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval 
            from @product) p
WHERE xmlval.exist('/Root/Node[.="2"]')=1
ORDER BY p.id_product


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 03/01/2013 :  04:09:51  Show Profile  Reply with Quote
But with this I get one output (1001) but 2 is also in id_product 1000
I actually don't want to go through the @mat table on this one.
Just the id_products with a 2 in type_mat
Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/01/2013 :  04:38:37  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

But with this I get one output (1001) but 2 is also in id_product 1000
I actually don't want to go through the @mat table on this one.
Just the id_products with a 2 in type_mat
Thanks


thats because you've a space before 2 for id 1000

do this small change and it will work fine


select distinct p.id_product
from @mat m
cross join (select id_product,cast('<Root><Node>'+ replace(replace(type_mat,' ',''),',','</Node><Node>') + '</Node></Root>' AS xml) AS xmlval 
            from @product) p
WHERE xmlval.exist('/Root/Node[.="2"]')=1
ORDER BY p.id_product


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000