Problem with the Query

Question

Post reply

Problem with the Query

RockingAdmin

SSCrazy

Points: 2828
More actions
May 6, 2009 at 12:48 am

#93470

hi all,
i have 150 fileds in a table. Among them, one field is name it consists of fistname and lastname "data". i created two columns.i want to split two firstname and lastname Data from one column to firstname column and lastname column.the firstname and lastname is divided by +symbol.before + in data i want to move to firstname and after+ in data into lastname.some times data is starting from +
for example: in name column data will be like this
ex. 1) dba+sqlserver
ex. 2)+dba
dba into firstname and sqlserver into lastname
secone ex +dba into firstname
like that
i tried to use the below statement,
update emp_data set firstname=substring(name,1,charindex('+',name)-1)
lastname=substring(name,charindex('+',name),len(name)) ;
i am getting error:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
please help me on this issue
thanks in advance
ROCK...

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

your code is correct

and try with close brackets

update emp_data

set firstname=substring(name,1,(charindex('+',name)-1)),

lastname=substring(name,(charindex('+',name)+1),(len(name)))

declare @abc varchar(10)

set @abc = 'ABCD+1234'

select substring(@ABC,1,(charindex('+',@ABC)-1))

select substring(@ABC,(charindex('+',@ABC)+1),(len(@ABC)))

ARUN SAS

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

You're not handling the case where there isn't a '+', try this

update emp_data set firstname=substring(name,1,charindex('+',name+'+')-1)

lastname=substring(name,charindex('+',name+'+'),len(name)) ;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

RockingAdmin SSCrazy Points: 2828 More actions · Answer 3

hi mark

the above he said its not working.

update emp_data

set firstname=substring(name,1,(charindex('+',name)-1)),

lastname=substring(name,(charindex('+',name)+1),(len(name)))

you are right, can you help me with the code to modify.

its urgent

thanks

ROCK...

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi Rock

Try this:

DECLARE @t TABLE (name VARCHAR(100), first_name VARCHAR(50), last_name VARCHAR(50))

INSERT INTO @t (name)

SELECT 'hello+world'

UNION ALL SELECT '+foo'

UNION ALL SELECT 'bar+'

UPDATE @t SET

first_name = SUBSTRING(name, 1, CHARINDEX('+', name, 1) - 1),

last_name = SUBSTRING(name, CHARINDEX('+', name, 1) + 1, LEN(name))

SELECT * FROM @t

Greets

Flo

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Did you try the code I posted?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

RockingAdmin SSCrazy Points: 2828 More actions · Answer 6

hi mark

i have tried oyur code. same error occured.

thanks

ROCK..

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Hi

Could you please just copy/paste my query without any manipulations and tell me if it works?

As Mark already wrote, if you still get errors, please provide the exact error message and some test data. You can find a link in my signature which will help to create test data.

Greets

Flo

nageswara.dhaveji SSChasing Mays Points: 601 More actions · Answer 8

HI,

Try the below code.....

create table #temp (name VARCHAR(100), first_name VARCHAR(50), last_name VARCHAR(50))

INSERT INTO #temp (name)

SELECT 'FIRST+LAST'

UNION ALL SELECT '+LAST'

UNION ALL SELECT 'FIRST+'

UPDATE #temp SET first_name = SUBSTRING(name, 1, patINDEX('%+%', name)-1),

last_name = SUBSTRING(name, PATINDEX('%+%', name)+ 1, LEN(name))

SELECT * FROM #temp

:rolleyes:

RockingAdmin SSCrazy Points: 2828 More actions · Answer 9

hi Florian Reischl,

i am gettign the same error,

Msg 536, Level 16, State 5, Line 5

Invalid length parameter passed to the SUBSTRING function.

The statement has been terminated.

for '+' every hting is ok

and this kind of data i am not able to modify

this is the sample data

test data::

/o Den ing/Review~^10 wayLane.UK

43. a. raj. ~^8th floor, crooss Group

223456 pix Road.

3885 West 385 Road

uol Box 2215~^1870 water way

3888 s 650 e

a

400 2nd ave s

175 thomson Street.

~^2001 central park station drive. uk

RockingAdmin SSCrazy Points: 2828 More actions · Answer 10

hi nageswara

same error boss

any idea please check the test data in my earlier mail

thanks

ROCK...

Stefan Oude Vrielink Mr or Mrs. 500 Points: 561 More actions · Answer 11

I think the problem occurs here: firstname=substring(name,1,charindex('+',name)-1)

Whenever there is no "+" in the name column, the command will look like substring(name,1,-1).

Check existance of "+" first, before using the substring function.

nageswara.dhaveji SSChasing Mays Points: 601 More actions · Answer 12

Hi,

I have modified my query which I have posted earilier... try this ...

create table #temp (name VARCHAR(100), first_name VARCHAR(50), last_name VARCHAR(50))

truncate table #temp

INSERT INTO #temp (name)

SELECT 'FIRST+LAST'

UNION ALL SELECT '+LAST'

UNION ALL SELECT 'FIRST+'

UNION ALL SELECT 'FIRST name'

UPDATE #temp SET first_name = SUBSTRING(name, 1, case isnull(patINDEX('%+%', name),0) when 0 then len(name) else

patINDEX('%+%', name)-1 end),

last_name = case isnull(patindex('%+%', name),0) when 0 then ' ' else SUBSTRING(name, PATINDEX('%+%', name)+ 1, LEN(name)) end

SELECT * FROM #temp

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 13

rockingadmin (5/7/2009)
hi Florian Reischl,
i am gettign the same error,
Msg 536, Level 16, State 5, Line 5
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
for '+' every hting is ok
and this kind of data i am not able to modify
this is the sample data
test data::
/o Den ing/Review10 wayLane.UK
43. a. raj. ~^8th floor, crooss Group
223456 pix Road.
3885 West 385 Road
uol Box 2215~^1870 water way
3888 s 650 e
a
400 2nd ave s
175 thomson Street.
~^2001 central park station drive. uk

Is this what you want?

DECLARE @emp_data TABLE (name VARCHAR(100), firstname VARCHAR(50), lastname VARCHAR(50))

INSERT INTO @emp_data (name)

SELECT 'dba+sqlserver' UNION ALL

SELECT '+dba' UNION ALL

SELECT '/o Den ing/Review10 wayLane.UK' UNION ALL

SELECT '43. a. raj. 8th floor, crooss Group' UNION ALL

SELECT '223456 pix Road.' UNION ALL

SELECT '3885 West 385 Road' UNION ALL

SELECT 'uol Box 22151870 water way' UNION ALL

SELECT '3888 s 650 e' UNION ALL

SELECT 'a' UNION ALL

SELECT '400 2nd ave s' UNION ALL

SELECT '175 thomson Street.' UNION ALL

SELECT '2001 central park station drive. uk '

UPDATE @emp_data

SET firstname=SUBSTRING(name,1,CHARINDEX('+',name+'+')-1),

lastname=SUBSTRING(name,CHARINDEX('+',name+'+'),LEN(name)) ;

SELECT name,firstname,lastname

FROM @emp_data

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

arun.sas SSChampion Points: 11831 More actions · Answer 14

Hi,

try this

declare @abc varchar(10)

set @abc = '1234'

select substring(@ABC,1,case when (charindex('+',@ABC)-1)< 0 then 0 else (charindex('+',@ABC)-1)end)

select substring(@ABC,(charindex('+',@ABC)+1),(len(@ABC)))

ARUN SAS