jingamells
-
Posts
3 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by jingamells
-
-
On 6/16/2019 at 10:54 PM, Funce said:
If the highlighted numbers match every time, then could I recommend placing them in separate fields when they're inserted?
That way you can use a proper SQL JOIN with all the efficiencies associated with it?
If this isn't an option , then you can use SUBSTRING_INDEX if you're using MYSQL
SELECT SUBSTRING_INDEX(field, '*', 2) FROM table
that'll give you the string up to the second *.
Hi Funce,
Thank you for the suggestion, however Subtring_index I am unable to use. Is there an alternative? I have tried various combinations of char_index, substring but struggling to incorporate the end point of the string that I need.Here are some of the values in the field:
BD8K1 691758*6*201808201650*QNURMG
BD8K1 482291*24*201903261010*QNURPC
BD8K1 319146*42*201804170930*QTENS
BD8K1 19864*35*201705160930*QTENS
BD8K1 433070*9*201712190930*QTENS
BD8K1 529574*33*201709190930*QTENS
TIA -
Hi,
I am trying to join two tables that have similar fields, but in slightly different formats. I was previously using something along the lines oflike '%*%' Then SUBSTRING(b.fieldname,CHARINDEX(b.fieldname,'*',1)+1,9)
However the format has now changed and I am struggling to put something together.
Table A
BD8K1 23161*35*201904231110*QULN
Table B
23161*35*1
However the number of characters in both could vary so cant use that.
Table A
BD8K1 242875*10*201904251015*NBB1
Table B
242875*10*113
Is there some code that could be written that's something like:
A – From the space use the value up to and including the 2nd * (BD8K1 is at the beginning of every value in table A so could potentially use from character 7)
B – Use the value up to and including the 2nd *
Any help would be very much appreciated.
ThanksJames
Date Format
in SQL
Posted
Hi,
I am using the below to try and change a string of dd/mm/yy hh:mm to yyyymmdd and getting the following error: 'Conversion failed when converting date and/or time from character string.'
convert(varchar(8),cast(ArrivalDateTime as date),112) as ArrivalDateTime,
what am i doing wrong?
TIA
James