CODEWITHSUNDEEP
sqlsql-server
John Kamaal
John Kamaal

Reputation: 129

How to write a SQL query to delete the first element (the very first character) of a path

I have a table that contains a column for images which stores the link of images.

The example looks like this: 

\\Images/image1.jpg

Now assume my database has more than 1000 records for 1000 images which is working fine, but now due to some requirement change, I have to update the links.

I want to remove the first slash from the all 1000 records via a SQL query.

Now it is stored like this:

\\Images/image1.jpg

and I want to convert it to

\Images/image1.jpg

One approach is to go to 1000 records and remove it one by one, which is too time consuming, as an alternative what could be a SQL query that could be run via SSMS?

Upvotes: 0

Views: 279

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would not use replace for this purpose! You want to remove the first slash, and replace() replaces all matching values everywhere in the string.

Plus, SQL Server has a very simple function to do this:

update t
    set col = stuff(col, 1, 1, '')
    where col like '\\%';

Upvotes: 0

umair qayyum
umair qayyum

Reputation: 286

If you are just looking to fetch your data set like this, then try using RIGHT function in your select query; your query should look something like this;

SELECT RIGHT(path, LEN(path)-1) FROM yourtable

If you want to update your table with updated values than use the UPDATE query, should look something like this;

UPDATE yourtable SET path = RIGHT(path, LEN(path)-1) FROM yourtable

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51831

To only update values that start with "\\" you can do

UPDATE someTable SET col = REPLACE(col, '\\%', '\')

Upvotes: 2

KeizerHarm
KeizerHarm

Reputation: 330

This should do it on Ms SQL.

SELECT SUBSTRING(path, 2, LEN(path) - 1) FROM yourtable

EDIT:

UPDATE yourtable
SET path = SUBSTRING(path, 2, LEN(path) - 1)
WHERE SUBSTRING(path, 1, 2) = '\\'

Upvotes: 1

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

You can use REPLACE to do this:

update yourTable
set yourColumn = replace(yourColumn,'\\','\')

Upvotes: 2

Related Questions