i have 2 column, how can i ....

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)

i have 2 column, how can i ....

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2009-07-06 : 08:54:07

Hi

I have 2 column in my table such as col1, col2

col1 contains data
like
col1
Test / Test2
tt/tre
tret/tert
trejlk /jfsdlfs

now i want like as below
col1 col2
test /Test2
tt /tre
tret /tert
trejlk /jfsdlfs

how can i get ?

data from slash(/) must be in col2 & deleted from col1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 08:56:18

you want to split the data in col1 into col1 and col2 at the "/" character ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-06 : 08:57:14

select left(data,charindex('/',data)-1) as col1,substring(data,charindex('/',data)+1,len(data)) as col2 from table

Madhivanan

Failing to plan is Planning to fail

asifbhura
Posting Yak Master

165 Posts

Posted - 2009-07-06 : 08:57:15

yes
& from col1 it must be deleted permenantly & it must be in col2 permenantly

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 08:59:29

[code]
update t
set col1 = left(col1, charindex('/', col1 + '/') - 1),
col2 = ltrim(right(col1, len(col1) - charindex('/', col1 + '/')))
from table t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

asifbhura
Posting Yak Master

165 Posts

Posted - 2009-07-07 : 01:08:35

it doesnt work, it gives error like below

Invalid length parameter passed to the substring function.
The statement has been terminated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 01:10:03

quote:
Originally posted by asifbhura

it doesnt work, it gives error like below

Invalid length parameter passed to the substring function.
The statement has been terminated.

post the query that you used

KH
[spoiler]Time is always against us[/spoiler]

asifbhura
Posting Yak Master

165 Posts

Posted - 2009-07-07 : 01:26:38

update sheet1
set FullName = left(FullName, charindex('/', FullName + '/') - 1),
initialName = ltrim(right(FullName, len(FullName) - charindex('/', FullName + '/')))
from sheet1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 01:29:35

quote:
Originally posted by asifbhura

Invalid length parameter passed to the substring function.

The error means there are records without a slash.

N 56°04'39.26"
E 12°55'05.63"

asifbhura
Posting Yak Master

165 Posts

Posted - 2009-07-07 : 01:32:49

then how to execute these query only with slash(/) found records

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 01:40:56

[code]
update sheet1
set FullName = left(FullName, charindex('/', FullName + '/') - 1),
initialName = ltrim(right(FullName, len(FullName) - charindex('/', FullName)))
from sheet1[/code]

Note : removed the concat of '/' in the charindex of the right() section

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 01:43:38

quote:
Originally posted by asifbhura

then how to execute these query only with slash(/) found records

these will not give error. The fullname & initial will be empty string

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 01:44:54

[code]
DECLARE @sheet1 TABLE
(
FullName varchar(20),
initialName varchar(10)
)

INSERT INTO @sheet1 (FullName)
SELECT 'Test / Test2' UNION ALL
SELECT 'tt/tre' UNION ALL
SELECT 'tret/tert' UNION ALL
SELECT '/' UNION ALL
SELECT 'testtest' UNION ALL
SELECT 'trejlk /jfsdlfs'

UPDATE @sheet1
SET FullName = left(FullName, CHARINDEX('/', FullName + '/') - 1),
initialName = LTRIM(right(FullName, LEN(FullName) - CHARINDEX('/', FullName)))
FROM @sheet1

SELECT *
FROM @sheet1

/*
FullName initialName
-------------------- -----------
Test Test2
tt tre
tret tert

testtest testtest
trejlk jfsdlfs

(6 row(s) affected)
*/

[/code]

KH
[spoiler]Time is always against us[/spoiler]

os_yadav
Starting Member

4 Posts

Posted - 2009-07-07 : 04:48:59

DECLARE @sheet1 TABLE
(
FullName varchar(20),
initialName varchar(10)
)

INSERT INTO @sheet1 (FullName)
SELECT 'Test / Test2' UNION ALL
SELECT 'tt/tre' UNION ALL
SELECT 'tret/tert' UNION ALL
SELECT '/' UNION ALL
SELECT 'testtest' UNION ALL
SELECT 'trejlk /jfsdlfs'

UPDATE @sheet1
SET FullName = left(FullName, CHARINDEX('/', FullName + '/') - 1),
initialName = case when charindex('/',fullname)> 0 then LTRIM(right(FullName, LEN(FullName) - CHARINDEX('/', FullName))) else '' end
FROM @sheet1

SELECT *
FROM @sheet1

SYSTEM ANALYST

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources