Is it possible to do something like this? Essentially I want to cast a int into a string and used the string on a join. Pay attention to the %t1.id%
select t2.*
from t1
join t2 on t2.url='site.com/path/%t1.id%/more'
where t1.id > 9000
Add a comment
|
6 Answers
If you have a column called "col1" which is int, you cast it to String like this:
CONVERT(col1,char)
e.g. this allows you to check an int value is containing another value (here 9) like this:
CONVERT(col1,char) LIKE '%9%'
Comments
You could use CONCAT, and the numeric argument of it is converted to its equivalent binary string form.
select t2.*
from t1 join t2
on t2.url=CONCAT('site.com/path/%', t1.id, '%/more') where t1.id > 9000
Comments
Try it using CONCAT
CONCAT('site.com/path/','%', CAST(t1.id AS CHAR(25)), '%','/more')
Comments
select t2.*
from t1 join t2 on t2.url='site.com/path/%' + cast(t1.id as varchar) + '%/more'
where t1.id > 9000
Using concat like suggested is even better though
Comments
You can do this:
select t2.*
from t1
join t2 on t2.url = 'site.com/path/' + CAST(t1.id AS VARCHAR(10)) + '/more'
where t1.id > 9000
Pay attention to CAST(t1.id AS VARCHAR(10)).
1 Comment
Daniel Adeniji
It does not appear that one can cast to varchar in MySQL. Though same exact syntax works in Transact SQL ( Microsoft SQL Server ). Posted about same here: MySQL:- Cast Integer to String learningintheopen.org/2020/11/11/mysql-cast-integer-to-string
I've ended here while doing some research, not exactly for this reason, but I love this kind of "puzzles" :-)
Here's my version of a random vehicle plate generator, it's inspired from European plates (inspired - not an exact copy!).
-- (AA) (BBCCCC) ['YY]
-- \1 \2 \3 \4
--
-- 1 = year related series
-- 2 = Random MD5 string
-- 3 = Timestamp based MD5 string (uniqueness)
-- 4 = year of registration (shared with part 1)
SELECT
UPPER(
CONCAT(
LEFT(
TO_BASE64(
@rndVehicleYear:= RIGHT(
CONVERT(
floor(rand() * (year(current_date) - (@minYear := 1980)) + @minYear),
CHAR
),
2
)
),
2
),
SPACE(1),
LEFT(MD5('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 2),
LEFT(MD5(unix_timestamp()), 4),
SPACE(2),
CONCAT('`', @rndVehicleYear)
)
)
AS RandomVehiclePlate;
Explained in simple words, a random Year is selected between 1980 and the current one: it will be used as a seed to create the first 2 Chars, but also in clear text, at the end of the plate.
Two more chars are random MD5, and the last 4 ensure that the plate is unique (that's my goal, at least), by generating chars on the currrent timestamp.
You can remove @rndVehicleYear := RIGHT( <- this and keep the full year instead of the last two numbers.
Cheers!
