String Question

Question

Post reply

String Question

thgamble1

Ten Centuries

Points: 1212
More actions
December 29, 2005 at 2:33 pm

#364501

I can't recall how to do this but I'm confident I'm overlooking something.
My problem is that I need to search through a field and find where a character value of _ (underscore) exists.
Once that is done I need to take the left(field,stringvalue from above-1) and update a field.
I'm including my rudimentary "code" for this to hopefully help shed some light.
declare @t int
set @t=(select max(counter) from [stats-2])
while @t>0
begin
declare @y int
set @y=(select charindex('_',childcamp) from [stats-2] where counter=@t)
set @y=@y-1
update [stats-2] set dept=left(childcamp,@y) where counter=@t
set @t=@t-1
end

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

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

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

CHARINDEX/PATINDEX can give you the position of a search string. check out BOL for more info.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

thgamble1 Ten Centuries Points: 1212 More actions · Answer 2

set @y=(select charindex('_',childcamp) from [stats-2] where counter=@t)

I'm using the charindex, but what's happening is that it seems to be basically ignoring my where clause and not giving me the different charindex's record by record for my update statement.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

This is a lot easier without the procedure code such as the while loop and no variables are needed.

create table stats_2

(Counterinteger not null primary key

, childcampvarchar(255) not null

, deptvarchar(255)

)

insert into stats_2

(Counter, childcamp)

select 1 , 'A_1234' union all

select 2 , 'AB_1234' union all

select 3 , 'ABC_1234' union all

select 4 , 'ABCD_1234_'

update stats_2

set dept = substring(childcamp, 1 , charindex('_',childcamp) - 1 )

select * from stats_2

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

Thgamble1,

Carl has the correct answer here... did it work for you or do you still have a question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thgamble1 Ten Centuries Points: 1212 More actions · Answer 5

With some modifications to my other structures, yes it worked.

Thanks much everyone!