CODEWITHSUNDEEP
mysql
user34537
user34537

Reputation:

int to string in MySQL

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

Upvotes: 41

Views: 205305

Answers (6)

funder7
funder7

Reputation: 1830

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!

Upvotes: 1

Andreas L.
Andreas L.

Reputation: 2923

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%'

Upvotes: 66

Bertzor
Bertzor

Reputation: 163

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

Upvotes: 5

xdazz
xdazz

Reputation: 160833

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

Upvotes: 25

John Woo
John Woo

Reputation: 263723

Try it using CONCAT

CONCAT('site.com/path/','%', CAST(t1.id AS CHAR(25)), '%','/more')

Upvotes: 6

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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)).

Upvotes: 2

Related Questions