separate a text in a field - 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 2005 Forums

Transact-SQL (2005)

separate a text in a field

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-10 : 01:37:45

I have a text in a field of my table like this : number-number-number(ex: 1234-12-57) i want to separate each number and insert into the different fields. how can i do this? (the length of digits are different).

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-10 : 01:53:08

Try this

Assuming only three parts in ur column(1234-12-57)

declare @table table (col1 int,col2 int ,col3 int)
insert into @table(col1,col2,col3)
select parsename(replace(column,'-','.'),3),parsename(replace(column,'-','.'),2),parsename(replace(column,'-','.'),1) from urtable

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-10 : 02:12:09

jai's solution is correct,
if u have 3'-' and above then add parsename(column,4)----- in the select List.

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-10 : 02:17:02

can u explain it more? suppose that my table name is tbl1 and the name of the field which has the information in it, is house_No and the three fields are block_no & land_no & apartment_no.
how should i write the query above?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-10 : 02:19:02

quote:
Originally posted by Exir

can u explain it more? suppose that my table name is tbl1 and the name of the field which has the information in it, is house_No and the three fields are block_no & land_no & apartment_no.
how should i write the query above?

declare @table table (block_no int,land_no int ,apartment_no int)
insert into @table(block_no ,land_no,apartment_no)
select parsename(replace(house_No ,'-','.'),3),parsename(replace(house_No ,'-','.'),2),parsename(replace(house_No ,'-','.'),1) from tbl1

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-10 : 02:25:53

if u have these fields in block_no ,land_no,apartment_no in tbl1
then use this

update tbl1
set block_no = parsename(replace(house_No ,'-','.'),3),land_no=parsename(replace(house_No ,'-','.'),2),apartment_no = parsename(replace(house_No ,'-','.'),1)

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-10 : 02:30:57

thanks alot
but it doesnt update my table(tbl1),i want the three fields: block_no & land_no & apartment_no in tbl1 be updated by this query. how should i do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:31:31

[code]
SELECT t.PK,
MAX(CASE WHEN f.ID=1 THEN f.Val ELSE NULL END) AS firstvalue,
MAX(CASE WHEN f.ID=2 THEN f.Val ELSE NULL END) AS secondvalue,
MAX(CASE WHEN f.ID=3 THEN f.Val ELSE NULL END) AS thirdvalue
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.col,'-')f
GROUP BY t.PK
[/code]

parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-10 : 02:33:51

Try This to update ur columns

update tbl1
set block_no = parsename(replace(house_No ,'-','.'),3),
land_no=parsename(replace(house_No ,'-','.'),2),
apartment_no = parsename(replace(house_No ,'-','.'),1)

Jai Krishna

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-10 : 02:34:05

thank you very much bklr and other guys. iy worked :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:34:09

quote:
Originally posted by Exir

thanks alot
but it doesnt update my table(tbl1),i want the three fields: block_no & land_no & apartment_no in tbl1 be updated by this query. how should i do it?

show the query used. i think last posted suggestion will work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources