how to create this proc?

Question

how to create this proc?

SQLRep

SSCrazy

Points: 2052
More actions
June 23, 2005 at 1:27 pm

#167304

Hi all,

I am trying to create procedure that will separate field from concatinated fields. such as “21.test”. The “test” is really the Department, and the 21 is the Project.
How can i separate these from one field.
Help:
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You want to correct the design and split that column?

Select Left(FieldName, charindex('.', FieldName, 1) - 1) as Project, Right (FieldName, LEN(FieldName) - charindex('.', FieldName, 1)) as Departement

SQLRep SSCrazy Points: 2052 More actions · Answer 2

hi,

yes, currently they have the 21.test in one column and now they want to split them and put it in to separat such as 21 in project column and test in department column.

thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Update YourTable SET Project = Left(FieldName, charindex('.', FieldName, 1) - 1), Departement = Right (FieldName, LEN(FieldName) - charindex('.', FieldName, 1))

You might wanna make sure that the project is numeric in all the rows to make sure this doesn't fail (using my previous select as a starting point)

SQLRep SSCrazy Points: 2052 More actions · Answer 4

Hi,

not it's not numeric!! it's varchar!! how can i make change on to that!!

Thanks

SQLRep SSCrazy Points: 2052 More actions · Answer 5

hi in

the last response which is update statement i says

Invalid length parameter passed to the substring function.

thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

It probabely means that there's no period in the field... maybe you could check it out with this query :

Select * from dbo.YourTable where charindex('.', YourField, 1) = 0

To correct the problem in the update you can use this technique where there's a len() -1

do ABS(LEN(something) - 1) and the error should go away... but that would also mean that the data is wrong.