SQL Replace some text of each string of a column

Question

I have a database table that contains a column named: "shortLink". This column contains a short link of an address of each row in the table. I use the tinyurl.com services for the short links. The short link looks like this: https://tinyurl .com/randomletters.

Recently I figured out that I need to change the shortlinks to their preview short link version: https://preview.tinyurl .com/randomletters.

The only difference between the two link formats is that there is a prefix preview. between https:// and tinyurl.

Since I have hundreds of rows in the sql table I cannot fix this manually. Is there any way to convert each shortlink (by adding the prefix preview. in the address) to its preview format with code in sql?

Thanks.

PS - Notice that there is a gap between tinyurl and .com in the link formats above. This gap is added intentionally because the forum would not let me publish the question otherwise.

dev.mysql.com/doc/refman/8.0/en/…

Paul Spiegel
– Paul Spiegel

2018-05-10 12:45:05 +00:00
Commented May 10, 2018 at 12:45 — Paul Spiegel
– Paul Spiegel, Commented May 10, 2018 at 12:45

Brad · Accepted Answer · 2018-05-10 12:49:22Z

1

-- this will update the field for you where it does not already have the preview. in it.

UPDATE YourTable
SET  shortlinks= REPLACE( shortlinks, 'https://tinyurl .com', 'https://preview.tinyurl .com')
WHERE  shortlinks NOT LIKE 'https://preview.tinyurl%'

edited May 10, 2018 at 12:49

answered May 10, 2018 at 12:45

Brad

3,7253 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-05-10 12:45:16Z

0

You can just use an update:

update t
    set shortlink = concat('http://preview.', substring(shortlink, 8))
    where shorlink like 'http://tinyurl%';

answered May 10, 2018 at 12:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

M Danish · Accepted Answer · 2018-05-10 13:17:58Z

0

Bro try this. first, remove all spaces in URL on anywhere then replace 'tinyurl' to 'preview'

UPDATE [Table_Name]
SET  shortLink=  REPLACE(REPLACE( shortLink, ' ', ''),'tinyurl', 'preview')

edited May 10, 2018 at 13:17

answered May 10, 2018 at 13:15

M Danish

4782 silver badges5 bronze badges

Collectives™ on Stack Overflow

SQL Replace some text of each string of a column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related