Data manipulation - Any idea! - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Data manipulation - Any idea!

Author

Topic

azmi
Starting Member

37 Posts

Posted - 2006-04-18 : 05:10:26

[code]
Hi guys, do you have any idea how to retrieve packing_mode from description field.
Is that possible cause description length is not consistant. Your idea is welcome...

description packing_mode
----------- ------------
Fudgio 75x24:Special Goods 75x24
Sesamio 150x12:Special Goods 150x12
Cheesti 225x12:Special Goods 225x12
Cream Crk 400gx12:Special Goods 400gx12
Margie 400gx12:Special Goods 400gx12

[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-18 : 06:03:38

Hi azmi,

This works with your sample data. It uses the colon and the space prior to it, so if you have data which does not fit that, then this may not work...

--data
declare @t table (description varchar(50))
insert @t
          select 'Fudgio 75x24:Special Goods'
union all select 'Sesamio 150x12:Special Goods'
union all select 'Cheesti 225x12:Special Goods'
union all select 'Cream Crk 400gx12:Special Goods'
union all select 'Margie 400gx12:Special Goods'

--calculation
select description, right(ChoppedToColon, charindex(' ', reverse(ChoppedToColon))-1) as packing_mode
from (select description, left(description, charindex(':', description)-1) as ChoppedToColon from @t) a

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 06:04:39

Cant you use Front end application to split the data and extract accordingly?
Otherwise you can consider this approach



Select  description,
	reverse(substring(reverse(substring(description,1,charindex(':',description,1)-1)),1,
	charindex(' ', reverse(substring(description,1,charindex(':',description,1)-1)))-1)) as Packing_code
from
(
	select 'Fudgio 75x24:Special Goods' as description  union all
	select 'Sesamio 150x12:Special Goods' union all
	select 'Cheesti 225x12:Special Goods'  union all
	select 'Cream Crk 400gx12:Special Goods' union all
	select 'Margie 400gx12:Special Goods'   
) T

Also coery has excellant function to retreive whatever you want
Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-18 : 06:13:39

I'm sure it can be done easier, but:

[code]
create table #tbl (description varchar(100))

insert into #tbl
select 'Fudgio 75x24:Special Goods'
union select 'Sesamio 150x12:Special Goods'
union select 'Cheesti 225x12:Special Goods'
union select 'Cream Crk 400gx12:Special Goods'
union select 'Margie 400gx12:Special Goods'

select ltrim(reverse(substring(reverse(substring(description,1,charindex(':',description))),2,charindex(' ',reverse(substring(description,1,charindex(':',description))))-1))) from #tbl

drop table #tbl
[code]

EDIT: Got double sniped, too much work got in the way..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources