CODEWITHSUNDEEP
mysqlsqldatabasereplace
Adam
Adam

Reputation: 186

Find and replace or delete part of string in SQL

I have a dataset in sql and I would like to remove certain part of the data

I have searched online and found that find and replace could be used for what I want but being fairly new to sql I am not sure if I am approaching it correctly

SELECT REPLACE('SQL Tutorial', 'T', 'M');

How do I do a simple 'Find and Replace" in MsSQL? https://www.w3schools.com/sql/func_sqlserver_replace.asp

To keep things simple, here is a sample of the dataset

column name userdetails: [{value={ "name":"Alice", "id":"69", "status": "active"}}]

I want to remove [{value= from the start of the data and }] from end of the data. The end goal is to have this:

{ "name":"Alice", "id":"69", "status": "active"}

This is how I am trying to approach it:

SELECT * FROM REPLACE("mytable",'[{value=','');

Actual: [{value={ "name":"Alice", "id":"69", "status": "active"}}]

expected: { "name":"Alice", "id":"69", "status": "active"}

Upvotes: 1

Views: 177

Answers (1)

xavier
xavier

Reputation: 2040

If you use SELECT SUBSTR(NAME_OF_THE_COLUMN, 8, LENGTH(NAME_OF_THE_COLUMN)-9) FROM NAME_OF_THE_TABLE you'll remove the first 8 characters and the last 2 characters, which is the desired result, I guess.

Example: Here you can play with it. Example:

enter image description here

Upvotes: 1

Related Questions