How to deal with * in Mysql

[tanyali]

in my table, there r some * like :

id name
1 aaa_***
2 abcddd
3 bbb_***
4 adaflkj

I wanna to change all * in this table to 9,
anybody who knows how to deal with * in Mysql.
Thanks,
Tanya

[Atli]

The * character inside strings is treated like any other character, so you would have to replace it with some script, or manually, just like you would do with any other character.

This character is used by MySQL as a wildcard character. It can be used to replace table names or column names in places where you would like MySQL to use all available tables/columns.

For example:

SELECT myTbl.* FROM myTbl;
GRANT ALL ON myDb.* to 'User';
 

[pbmods]

Have a look at the replace function.

[tanyali]

The * character inside strings is treated like any other character, so you would have to replace it with some script, or manually, just like you would do with any other character.

This character is used by MySQL as a wildcard character. It can be used to replace table names or column names in places where you would like MySQL to use all available tables/columns.

For example:

SELECT myTbl.* FROM myTbl;
GRANT ALL ON myDb.* to 'User';
 

Thanks for answering,
MySql use * as a wildcard character, this is the problem !
in a linux terminal , using " \ " before " * " means the character " * " itself instead of any other character, so I am wondering is there any similar way to do in MySql ?
Tanya

[pbmods]

MySql use * as a wildcard character, this is the problem !
in a linux terminal , using " \ " before " * " means the character " * " itself instead of any other character, so I am wondering is there any similar way to do in MySql ?

If the * is in quotes or backticks, it won't get processed as a wildcard. In other words:

SELECT '*' FROM `someTable`
SELECT `*` FROM `someTable`
SELECT * FROM `someTable`

The first one will return one column, one row.
The second one will probably return an error, unless you have a field explicitly named `*` in `someTable`.
The third one will return all fields in `someTable`.

[tanyali]

If the * is in quotes or backticks, it won't get processed as a wildcard. In other words:

SELECT '*' FROM `someTable`
SELECT `*` FROM `someTable`
SELECT * FROM `someTable`

The first one will return one column, one row.
The second one will probably return an error, unless you have a field explicitly named `*` in `someTable`.
The third one will return all fields in `someTable`.

Yes !
this is actually what I want to get !
but how about inside of a string ?

I tried these : code (mysql )

1- delete from fcst where name = '???_`***`' ;
2- delete from fcst where name = '???_'***'' ;
3- delete from fcst where name = '*_`***`' ;
but none works.
I wanna to delete all records which end with *** .

Tanya

[pradeep kaltari]

Yes !
this is actually what I want to get !
but how about inside of a string ?

I tried these : code (mysql )

1- delete from fcst where name = '???_`***`' ;
2- delete from fcst where name = '???_'***'' ;
3- delete from fcst where name = '*_`***`' ;
but none works.
I wanna to delete all records which end with *** .

Tanya

Hi Tanya,
Try the following:

DELETE FROM table_name
WHERE col_name LIKE '%***'
 

% will try to match zero or more characters. The WHERE clause specifies that the col_name should end with ***. Also note that I have used LIKE operator, if you try to use "=" in place of LIKE the query will delete only the records where the value of col_name is '%***'.

I hope this helps you.

Regards,
Pradeep

[tanyali]

Hi Tanya,
Try the following:

DELETE FROM table_name
WHERE col_name LIKE '%***'
 

% will try to match zero or more characters. The WHERE clause specifies that the col_name should end with ***. Also note that I have used LIKE operator, if you try to use "=" in place of LIKE the query will delete only the records where the value of col_name is '%***'.

I hope this helps you.

Regards,
Pradeep

Hi Pradeep,

I solved this problem, thank you so much !

Tanya

[pradeep kaltari]

Hi Pradeep,

I solved this problem, thank you so much !

Tanya

Glad to help you.

Regards,
Pradeep