grab data from 1 column into different column

[blanderk]

Dear all,

i need some help here. currently i have 1 table with multiple field.
1 of the field name PackSize contain data like ;;5000;ml;; or ;;;;60;Tablet etc.
So, i want to ask expert out there,how to grab the value inside PackSize into my other field such as field Vol,Volunit,VolDnm,VolDnmUnit,Package,PackageUnit.


here some example :


Let say ;;5000;ml;;
then the data should be like this :

Packsize | Vol | Volunit | VolDnm | VolDnmUnit | Package | PackageUnit

;;5000;ml;;| <null> | <null> | 5000 | ml | <null> | <null>


let say ;;;;60;Tablet

Packsize | Vol | Volunit | VolDnm | VolDnmUnit | Package | PackageUnit

;;;;60;Tablet | <null> | <null> | <null> | <null> | 60 | Tablet

[amitpatel66]

Could you please let us know how many different types of units you have in the column data? For Eg: ml, tablets etc..?

[blanderk]

for VolUnit there are 3 types (mg,kg,g)
for VolDnm there are 2 types (cubic inch,ml)
and for packagingUnit there are 3 types (tablets,capsule,units)

i'm a beginner for sql server. Hope all of you could help me on these.

[blanderk]

and actually in my table contain 40K rows of data

[amitpatel66]

And in what format the data is stored in the column packsize?
I see for ml its 2 semi colons followed by a number and then a semicolon and a unit and for tablet I see 4 semicolons followed by a number and so on?...Can you post few more sample data for reference?

[blanderk]

PackSize field format is varchar (50)

here are my other sample data :

1 ) ;;5000;ml;;
2 ) 65;gm;;;;
3 ) ;;;;120;Capsule
4 ) ;;;;24;Tablet

each semicolons will separate the data into 6 different column. if there is no value between semicolon like this (;;20)....then the data stored into the 1st field and 2nd field will be null but in 3rd field will be 20

[ck9663]

Read this and modify the code the suit your requirement.

-- CK