1
Update [asset_loc] 
Set [column name] ='<after trimming all special characters in field  ** trim from start and trim from end>'
where <column name> like '<pick values that start or end with spl characters

enter image description here

Sample data:

  • , ,/ a/22/,,,
  • a/22../
  • /.,, a/22

Desired result after query execution:

a/22
FanoFN
  • 6,815
  • 2
  • 13
  • 33
Rajesh Subramanya
  • 7
  • 2
  • Post sample data as textual form, not as image. Post an attempted query. Run `SELECT Version();` to check exact MySQL version. – FanoFN Dec 23 '21 at 03:47
  • It looks fiddly enough that a regular expression would be needed: [How to do a regular expression replace in MySQL?](https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – Andrew Morton Dec 23 '21 at 10:18
  • Are you trying to get the result through MySQL query? Because I can see you've updated your sample data but you didn't post any attempted query. Makes me think that you're trying achieve this through a different method? In fact, are you actually using MySQL database or sql-server? – FanoFN Dec 24 '21 at 07:42
  • Firstly Iam using SQL Server databases where more than 470 databases are in a single instance and trying to acheive through sql query. If that is not possible then please suggest some alternative. Using vb.net as front end – Rajesh Subramanya Dec 25 '21 at 02:49
  • 1
    @RajeshSubramanya If you are using Microsoft SQL Server (which the tags on your question say you are *not* but your comment says you are), then you can use the [TRIM](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql) function: `UPDATE [tableName] SET [colName] = TRIM('.,/? ' FROM [colName])`. – Andrew Morton Dec 25 '21 at 10:25
  • Can you remove the mysql tag to avoid confusion? – Mary Dec 26 '21 at 05:37
  • Thanks. UPDATE [tableName] SET [colName] = TRIM('.,/? ' FROM [colName] has worked and task accomplished. – Rajesh Subramanya Dec 27 '21 at 10:35

0 Answers0