CODEWITHSUNDEEP
sqlcastingmariadb
Double
Double

Reputation: 13

SQL Cast Mariadb Query - Add to output before exporting to variable

I'm trying to update a stats server due to Player ID formats being changed.

This is the starting values which will be queried.

MariaDB [hlxce]> SELECT * FROM `hlstats_PlayerUniqueIds` WHERE playerId = 562;
+----------+----------------+---------+-------+
| playerId | uniqueId       | game    | merge |
+----------+----------------+---------+-------+
|      562 | [U:1:18893661] | cstrike |  NULL |
+----------+----------------+---------+-------+
1 row in set (0.00 sec)

I butchered this together which at least matches the uniqueId but isn't updating the communityId correctly.

MariaDB [hlxce]> SELECT
    ->           hlstats_PlayerUniqueIds.uniqueId,
    ->           CAST(MID(hlstats_PlayerUniqueIds.uniqueId,6,-2) AS unsigned) + CAST('76561197960265728' AS unsigned) AS communityId
    ->           FROM
    ->           hlstats_PlayerUniqueIds
    ->           WHERE
    ->           hlstats_PlayerUniqueIds.playerId = 562;
+----------------+-------------------+
| uniqueId       | communityId       |
+----------------+-------------------+
| [U:1:18893661] | 76561197960265728 |
+----------------+-------------------+
1 row in set, 1 warning (0.00 sec)
MariaDB [hlxce]>

In short, I just want to ignore the 5 left characters ( [U:1: ) and the 1st character on the right ( ] ). So in this example I'd have 18893661 and from there, I simply want to add 76561197960265728 to it and export it as $communityId

If it helps this was the original SQL statement that stopped working.

MariaDB [hlxce]> SELECT
    -> hlstats_PlayerUniqueIds.uniqueId,
    -> CAST(LEFT(hlstats_PlayerUniqueIds.uniqueId,1) AS unsigned) + CAST('76561197960265728' AS unsigned) + CAST(MID(hlstats_PlayerUniqueIds.uniqueId, 3,10)*2 AS unsigned) AS communityId
    -> FROM
    -> hlstats_PlayerUniqueIds
    -> WHERE
    -> hlstats_PlayerUniqueIds.playerId = 562;
+----------------+-------------------+
| uniqueId       | communityId       |
+----------------+-------------------+
| [U:1:18893661] | 76561197960265728 |
+----------------+-------------------+
1 row in set, 1 warning (0.01 sec)
MariaDB [hlxce]>

EDIT****

To clarify
uniqueId ( without [U:1: nor ] ) + 76561197960265728
(18893661 + 76561197960265728) = 76561197979159389 
76561197979159389 = communityId

I've never really tried to manipulate data like this so my apologies if its blatantly obvious.

Upvotes: 0

Views: 50

Answers (1)

samhita
samhita

Reputation: 3505

you can use SUBSTRING to extract the required string from uniqueId and then add 76561197960265728 to form communityId.

[U:1: => This constitutes of 5 characters, so SUBSTRING start position is from 6th character and then the extract length can be the entire length of uniqueId minus 5 character minus the ] i.e 1 which is at the end.

SUBSTRING(uniqueId, 6, LENGTH(uniqueId) - 5 -1)

Sample Query

    select playerId, uniqueId, game,
    cast(SUBSTRING(uniqueId, 6, LENGTH(uniqueId) - 5 -1 ) as UNSIGNED) + 76561197960265728 as communityId
from  players
where playerId = 562;

Outputs

playerId uniqueId game communityId
562 [U:1:18893661] cstrike 76561197979159389

Fiddle Demo

Upvotes: 1

Related Questions