phpmyadmin Question posted in
The official documentation can be found here.

I am struggling to remove the last 30 characters from data in a column – Phpmyadmin

I am looking to remove the last 30 characters from a colum within my database.

Column Name = gatewayid
Table Name = tblclients

I have created the below which works in the sense it shows me the result and it’s correct but it does not commit or change anything.

SELECT gatewayid, /* ANSI Syntax */SUBSTRING( gatewayid
FROM 1 
FOR CHAR_LENGTH( gatewayid ) -30 ) AS col_trimmed, /* MySQL Syntax */SUBSTRING( gatewayid, 1, CHAR_LENGTH( gatewayid ) -30 ) AS col_trimmed
FROM tblclients

What am I missing, I am a noob 🙂

I expect the data in the column to remove the last 30 characters from each row.

Tags:

2

Answers


  1. 0

    You could use 

    SELECT gatewayid
,/*ANSI Syntax*/ SUBSTRING(gatewayid FROM 1 FOR CHAR_LENGTH(gatewayid) - 30) col_trimmed_ansi
,/* MySQL Syntax*/ SUBSTRING(gatewayid, 1  CHAR_LENGTH(gatewayid) -30) AS col_trimmed_mysql
FROM tblclients

    and for update the table content 

      UPDATE tblclients
  set gatewayid = SUBSTRING(gatewayid FROM 1 FOR CHAR_LENGTH(gatewayid) - 30)
    Login or Signup to reply.
  2. 0

    You need to use update statement:

    update tblclients 
set gatewayid = substring( gatewayid, 1, char_length( gatewayid ) -30 );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search