I've got a table with a varchar(128) field called identifier. It is supposed to get a 3 letter identifier followed by an indexing number: At some point in the past, there was a bug and about 5,000 records were input as: Our numbers do not go that high, so these values are padded with zeros (0). I wrote out a

MySQL SubString Returns Integer

I’ve got a table with a varchar(128) field called identifier. It is supposed to get a 3 letter identifier followed by an indexing number:

ABC-1234

At some point in the past, there was a bug and about 5,000 records were input as:

ABC-12345

Our numbers do not go that high, so these values are padded with zeros (0).

I wrote out a script to make sure my Microsoft T-SQL lingo was going to work correctly in this MySQL database, but I was shocked to see, what looks like, MySQL adding my values together:

SELECT
  identifier,
  SUBSTR(cast(identifier as char),1,4) as 'p',
  SUBSTR(cast(identifier as char),6) as 'q',
  SUBSTR(cast(identifier as char),1,4)+SUBSTR(cast(identifier as char),6) as 'p+q'
FROM drawing_table where length(identifier)=9
order by identifier desc;

Here is a snippet of the output:

identifier,p,q,p+q
STO-00021,STO-,0021,21
STO-00020,STO-,0020,20
STO-00019,STO-,0019,19
STO-00018,STO-,0018,18
STO-00017,STO-,0017,17
STO-00016,STO-,0016,16
STO-00015,STO-,0015,15
STO-00014,STO-,0014,14
STO-00013,STO-,0013,13
STO-00012,STO-,0012,12
STO-00011,STO-,0011,11
STO-00010,STO-,0010,10
STO-00009,STO-,0009,9
STO-00008,STO-,0008,8
STO-00007,STO-,0007,7
STO-00006,STO-,0006,6
STO-00005,STO-,0005,5
STO-00004,STO-,0004,4
STO-00003,STO-,0003,3
STO-00002,STO-,0002,2
STO-00001,STO-,0001,1
STA-00166,STA-,0166,166
STA-00165,STA-,0165,165
STA-00164,STA-,0164,164
STA-00163,STA-,0163,163
STA-00162,STA-,0162,162
STA-00161,STA-,0161,161
STA-00160,STA-,0160,160
STA-00159,STA-,0159,159
STA-00158,STA-,0158,158
STA-00157,STA-,0157,157
STA-00156,STA-,0156,156
STA-00155,STA-,0155,155
STA-00154,STA-,0154,154
STA-00153,STA-,0153,153
STA-00152,STA-,0152,152
STA-00151,STA-,0151,151
STA-00150,STA-,0150,150
STA-00149,STA-,0149,149
STA-00148,STA-,0148,148
STA-00147,STA-,0147,147
STA-00146,STA-,0146,146
STA-00145,STA-,0145,145
STA-00144,STA-,0144,144
STA-00143,STA-,0143,143
STA-00142,STA-,0142,142
STA-00141,STA-,0141,141
STA-00140,STA-,0140,140
STA-00139,STA-,0139,139
STA-00138,STA-,0138,138

screen capture

Why is MySQL trying to add my string values?

Answer

Use CONCAT(SUBSTR(cast(identifier as char),1,4), SUBSTR(cast(identifier as char),6))

Advertisement

Answer