CODEWITHSUNDEEP
mysqlsqlcharacter
Yassine edouiri
Yassine edouiri

Reputation: 291

MYSQL query will remove characters from a string

I would like to implement a query in MYSQL that will remove the characters before the space

For example :

UA 016

i want to remove 'UA ' with the space , and only keep

016

here is the query statements:

update field_data_field_id set field_id_value = '.... what i need here';

Thanks in advance

Upvotes: 1

Views: 4110

Answers (3)

Tin Tran
Tin Tran

Reputation: 6202

UPDATE field_data_field_id 
SET field_id_value=SUBSTRING_INDEX(
                     SUBSTRING_INDEX(field_id_value,' ',2),' ',-1);

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

try this

 CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

 CREATE FUNCTION NumericOnly (val VARCHAR(255)) 
 RETURNS VARCHAR(255)
 BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
 WHILE idx > 0 DO
 IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
 SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
 SET idx = LENGTH(val)+1;
 END IF;
 SET idx = idx - 1;
  END WHILE;
  RETURN val;
  END;

Use it by calling the NumericOnly function like this:

select NumericOnly('1&2') as result;

Returns: "12"

select NumericOnly('abc987') as result;

Returns: "987"

Upvotes: 3

Mihai
Mihai

Reputation: 26784

UPDATE table SET column=REPLACE(column,SUBSTRING_INDEX(column,' ',1),'');

Fiddle

Assuming all values have a space.

Upvotes: 2

Related Questions