UPDATE Query problem

Question

Post reply

UPDATE Query problem

SwivelingLeaf

Old Hand

Points: 337
More actions
July 28, 2003 at 9:23 am

#80763

I need to fix an update query to update to middle of string. I think i can do this using the SUBSTRING function but i need to know what to use as the 3rd argument as all of my strings will be different lengths.
example:
SUBSTRING([field],3,%)
% representing multiple length strings.
thanks in advance.

"The grass is always greener over the septic tank." ~Leaf

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

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

Steve Jones - SSC Editor SSC Guru Points: 735611 More actions · Answer 1

What is the criteria for the 3rd argument? In other words, if you did each row, how would you compute the 3rd value.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

SwivelingLeaf Old Hand Points: 337 More actions · Answer 2

this is for a text field. the 3rd argument needs to represent a multiple length string and up untill this point I thought % would do this.

example:

SUBSTRING(FIELD,3,%) 'should return the following:

CR20AB = 20AB

CR509FDS = 509FDS

"The grass is always greener over the septic tank." ~Leaf

winash SSChampion Points: 11446 More actions · Answer 3

Is the third argument in the Substring function the length of the text field???...if so this should work :

Substring(Field,3,Datalength(Field))

SwivelingLeaf Old Hand Points: 337 More actions · Answer 4

Thank you.

I could not figure out how to return the data length.

final:

Substring(Field,3,(Datalength(Field)-2))

thanks again.

"The grass is always greener over the septic tank." ~Leaf

laperrej SSC Journeyman Points: 94 More actions · Answer 5

Or you could use RIGHT(Field, LEN(Field) - 2)

Note that DATALENGTH() will you give you twice the number of characters for unicode columns (i.e. it returns the number of bytes). LEN() returns the number of characters.

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Or if your field ahs a maximum length use that value. For instance a varchar(100) filed use 100 as the 3rd parameter. It cannot get more data than is actually there.

The reason I suggest this is that LEN and DATALENGTH will cause a performance hit for each row since they must calculate for that row.

Edited by - antares686 on 07/29/2003 04:15:59 AM

gregj Old Hand Points: 346 More actions · Answer 7

To get the length of a column, you can use the following function:

COLUMNPROPERTY( OBJECT_ID('tablename'),'columnname','PRECISION')

It seems the query optimizer should evaluate it only once instead of for every row.