April 4, 2006 at 1:00 pm
Dear all,
I have a value like ‘1234,45456,8888’ in one field . This value length can be change or it become like ‘1234, ,8888’ or ‘1234,45456,’
I need to create a view that dived this value in 3 field usin tat commona(,) in field value.
First field like 1234 and second field like 45456 and third like 8888.
Pls help me how I can divided one value in 3 field using a SELECT statement
April 4, 2006 at 1:54 pm
There are couple of ways of doing it...one is:
declare @table table (col1 varchar(1000))
insert into @table values ('1234,45456,8888')
insert into @table values ('1234, ,8888')
insert into @table values ('1234,45456,')
select
substring(col1, 1, charindex(',', col1) -1) as col1,
substring(col1, charindex(',', col1)+1, charindex(',', col1, charindex(',', col1)+1) - charindex(',', col1)-1) as col2,
substring(col1, charindex(',', col1, charindex(',', col1)+1)+1, len(col1)) as col3
from @table
--output
col1 col2 col3
1234 45456 8888
1234 8888
1234 45456
April 5, 2006 at 3:05 am
This seems like a violation of the first normal form. You probably shouldn't have stored your three values in one column in the first place...
Better to store them in three columns from start and creating one or more special views that CONCATENATES the three values into one column for the (hopefully few) applications that really require the data to be in this concatenated way.
This you will see is not just the (most often) 'correct' way of dealing with RDBMSes and DB design but you will also see that your performance will improve. Remeber that indices on you 'ThreeValuedColumn' are most but useless the way you have them now.
Hanslindgren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply