Declare and Set

27 Jul 2006 8:32 PM

john d

Is it possible to set a variable to equal what is in a database field for
each row?

I asked a similiar question previously but I was not clear on what I wanted.
With this example how can I set the @datestring = whatever is in the Notes
field of the COPACKER_USER.physical_inventory table? re: where I entered
't1.notes'

Declare @datestring varchar(40)
set @datestring = t1.notes
select substring(@datestring, charindex('exp:', @datestring)+4,
len(@datestring) - (charindex('exp:',
@datestring)) - 4)
from COPACKER_USER.physical_inventory t1

I get the following error:

Server: Msg 107, Level 16, State 2, Line 2
The column prefix 't1' does not match with a table name or alias name used
in the query.

Also tried this suggestion:

UPDATE COPACKER_USER.physical_inventory
SET Exp_Date = substring(Notes, patindex('%exp: %', Notes) + 5, (len(Notes)
- (patindex('%exp: %', Notes) + 5)))
WHERE Notes LIKE '%exp: %'

got this error:

Server: Msg 8116, Level 16, State 2,
Line 1
Argument data type text is invalid for argument 1 of len function.

27 Jul 2006 9:04 PM

Arnie Rowland

John,

Don't abandon the previous thread yet. Folks are still working on your
problem.

The main problem is that the Notes field datatype is text/ntext.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"john d" <jo***@discussions.microsoft.com> wrote in message
news:517190CA-6ACC-4A85-A209-E2627C0CE717@microsoft.com...
> Is it possible to set a variable to equal what is in a database field for
> each row?
>
> I asked a similiar question previously but I was not clear on what I
> wanted.
> With this example how can I set the @datestring = whatever is in the Notes
> field of the COPACKER_USER.physical_inventory table? re: where I entered
> 't1.notes'
>
> Declare @datestring varchar(40)
> set @datestring = t1.notes
> select substring(@datestring, charindex('exp:', @datestring)+4,
> len(@datestring) - (charindex('exp:',
> @datestring)) - 4)
> from COPACKER_USER.physical_inventory t1
>
> I get the following error:
>
> Server: Msg 107, Level 16, State 2, Line 2
> The column prefix 't1' does not match with a table name or alias name used
> in the query.
>
> Also tried this suggestion:
>
> UPDATE COPACKER_USER.physical_inventory
> SET Exp_Date = substring(Notes, patindex('%exp: %', Notes) + 5,
> (len(Notes)
> - (patindex('%exp: %', Notes) + 5)))
> WHERE Notes LIKE '%exp: %'
>
> got this error:
>
> Server: Msg 8116, Level 16, State 2,
> Line 1
> Argument data type text is invalid for argument 1 of len function.
>
>