Wildcard Character on INT field

Hi all,

In my database I wish to update X amount of rows in a table where the first character of all the data in a field I wish to UPDATE is, for example, is ‘3’.

So if I have the following in my database…

[B]id[/B]
20001
20002
30001
30002
30003
30004

the following would be updated - 30001, 30002, 30003, 30004

I have run a query like the following but am getting no joy :frowning:

UPDATE table
SET sestionid = 2
WHERE id = 3%;

Is this the correct syntax? Is the percentage sign used to represent a wildcard character, where It can represent an unlimited amount of characters that follow the number ‘3’?

Thanks

Tryst

Are any math functions being applied to ‘id’? If not, I’d suggest changing the field type to var.

(general rule of thumb)

Uhm, but its my primary key. Does that matter?

So can I not do a query that contains wildcard characters on INT based fields?

Thanks

Tryst

Oh!

What about ‘id > 30000 AND < 39999’

You have to put what you LIKE within quotes (even if it’s a string) otherwise you’ll get a syntax error.

UPDATE [i]table[/i]
SET sestionid = 2
WHERE id LIKE '3%';

In my database I wish to update X amount of rows in a table where the first character of all the data in a field I wish to UPDATE is, for example, is ‘3’.

So if I have the following in my database…

[b]id[/b]
20001
20002
30001
30002
30003
30004

the following would be updated - 30001, 30002, 30003, 30004
Be aware that this will also fit id’s like 3, 30, 306 etc., ie every id which starts with 3

frenzo - If I add quotes, the query runs, but nothing gets updated (there are no results).

Lats - Thats seems a logic solution.

Tryst

If your column is a NUMBER then you cannot do like on it.

you’d think so, wouldn’t you :wink:

but i just tested it on an integer column and it works fine!!

create table numericstring
( pk integer
, id varchar(8)
);
insert into numericstring (pk) values
 ( 20001 )
,( 20002 )
,( 30001 )
,( 30002 )
,( 30003 )
,( 30004 )
,( 37 )
,( 937 )
,( 21 )
,( 3 )
,( 222222 )
,( 3333 )
;
select * from numericstring;

update numericstring set id=pk;

select * from numericstring
 where pk like '3%'

just another reason to set the mysql love/hate dial to 11, eh

When i tried it it did and obviously also Rudy got a valid result.

Whether this makes sense or not is a totally different question, though…

select * from numericstring
where pk like ‘3%’

Thats worked!

So just to confirm - its good practice (or common practice) to make numerical based fields of type CHAR if there are no maths to be performed on that field?

Thanks

Tryst

integer columns are smaller than char(5), so there’s the space consideration, if you have large tables, large numbers, use integer

plus, integer indexes are faster than character

so, all in all, i would say don’t use char if you can use integer

In what you want to achieve you don’t have to (and can’t anyway) change your field from int to char.
For the search query using LIKE on numbers it’s certainly not a beautiful solution.
MySQL does automatic type conversion and value clipping (as good as possible), that’s why it’s working.

If this is very professional? Perhaps not. Question is, what are the alternatives to achieve what you want.