-2

i have this query to generate custom generated ID but I think this is incorrect please help me

SELECT CONCAT('STUDENT-',COUNT(Id), + 1) FROM `students`;
Glenn Deliquiado
  • 1
  • 2
  • 3
    Why don't you use a simple `INT IDENTITY` column? – Ivan Starostin Feb 28 '18 at 10:22
  • i have assigned the datatype of the ID Attribute to VARCHAR, just wanted to generate my own unique ID – Glenn Deliquiado Feb 28 '18 at 10:36
  • 2
    You have tagged SQL Server, but the back ticks around student make me think it is MySQL - can you confirm? Either way - don't use a varchar column for this - use a standard integer surrogate key - if you need to add `STUDENT-` to the start, just concatenate it with your ID **after** it has been auto generated. It is redundant to store `STUDENT-` every where that you need to refer back to this identifier. – GarethD Feb 28 '18 at 10:43
  • MySQL Duplicate - https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix – GarethD Feb 28 '18 at 10:45
  • SQL Server Duplicate - https://stackoverflow.com/questions/2177584/sqlserver-identity-column-with-text – GarethD Feb 28 '18 at 10:46
  • I think you made a mistake. Try the following `SELECT CONCAT('STUDENT-',COUNT(Id) + 1) FROM `students`;` – Sergey Menshov Feb 28 '18 at 10:47
  • I am sorry https://stackoverflow.com/users/1048425/garethd for the tags yes it is for mysql – Glenn Deliquiado Feb 28 '18 at 10:51
  • https://stackoverflow.com/users/8931450/leran2002 thanks but I would like to add date and time in between 'STUDENT-' and COUNT(ID) + 1 – Glenn Deliquiado Feb 28 '18 at 11:00

2 Answers2

0

Change your query like this

SELECT 
    'STUDENT-' + CAST(COUNT(1) + 1 AS VARCHAR(20))
FROM 
    Students
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
  • not working You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST(COUNT(1)+1 AS VARCHAR(20)) FROM Students LIMIT 0, 1000' at line 3 – Glenn Deliquiado Feb 28 '18 at 10:38
0

Select 'STUDENT-' + CAST(newid() as varchar(50)) from Students

krunal modi
  • 135
  • 10