Change the column that has URLs in one of the table to <a href='URL'></a> before inserting in other table

0
votes

I am writing a stored procedure that has three tables that are supposed to be joined. Say table1,table2 and table3. So first I join table1 and table2 on some condition and save the results in temp table. Then I join the temp table with table3 on some condition and insert the resultant table in a permanent table say table_p.

The problem here is: when I join table1 and table2, one of the column in table2 contains a URL link.I want to edit the data in the whole column and make it : URL link I tried different things link string concatenation in select statement.But I got no success.

the code where I write select statement is:

   SELECT @SQLNEW='SELECT TOP 20 
                  ''' + @abc + ''' AS abc, 
                  t1._Ps AS Ps,
                  t1.URLSTR AS URL,
                  ''' + @StartDate + ''' AS StartDate,
                  ''' + @EndDate + ''' AS EndDate,
                  ''' + @Active + ''' AS Active 
                  FROM
                  table1 t1'

I want the t1.URLSTR to be in the format. I tried doing :

''+ <a href="''+ t1.URLSTR AS URL +'' ">''+ t1.URLSTR AS URL +''</a>',

but it did not work!

Please help! Thank you!

1
mysqlstored-procedures
This is MySQL, right? – Strawberry
I don't believe SELECT TOP is valid syntax in MySQL. Also, what are you trying to do with all these repeated quotes? Did you write this statement or did you cut-and-paste from Google? – Air
The statement is working fine.I am selecting random 20 results from table 1 and the other things like @abc,@startdate are the inputs of the stored procedure that the user can give.So basically I am selecting 2 things from table1 t1._Ps and t1.URLSTR and other things are my input values and will perform insert into statement and execute it.Things are working fine I just want to make URLSTR as <a href> in the table itself. – LearningToCode

1 Answers

0
votes

Try this...

CONCAT('<a href="', t1.URLSTR, '">', t1.URLSTR, '</a>') AS URL

It's a little cleaner.

MySQL String Functions - CONCAT()