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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extract a number from a string with like

Author  Topic 

Lesombrero
Starting Member

43 Posts

Posted - 2013-02-28 : 04:20:34
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-28 : 04:38:35
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-28 : 04:52:06
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

43 Posts

Posted - 2013-02-28 : 06:16:23
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

52326 Posts

Posted - 2013-02-28 : 06:24:07
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

43 Posts

Posted - 2013-02-28 : 06:24:32
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

52326 Posts

Posted - 2013-02-28 : 06:29:57
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

43 Posts

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 10:00:12
welcome

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

Go to Top of Page

Lesombrero
Starting Member

43 Posts

Posted - 2013-02-28 : 11:30:58
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 11:39:22
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

43 Posts

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

Lesombrero
Starting Member

43 Posts

Posted - 2013-02-28 : 11:46:19
Thank you James K.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 12:01:47
You are very welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 12:47:12
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

43 Posts

Posted - 2013-03-01 : 01:51:01
Thanks for the tip visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 02:01:39
welcome

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

Go to Top of Page

Lesombrero
Starting Member

43 Posts

Posted - 2013-03-01 : 03:44:37
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

52326 Posts

Posted - 2013-03-01 : 03:50:49
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

43 Posts

Posted - 2013-03-01 : 04:09:51
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

52326 Posts

Posted - 2013-03-01 : 04:38:37
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
    Next Page

- Advertisement -