Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
8781 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
8781 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
52326 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
52326 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
52326 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

3873 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

3873 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
52326 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
52326 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
52326 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
52326 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
 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.16 seconds. Powered By: Snitz Forums 2000