SQL Join Code Help

[jingamells]

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 of 

like '%*%' 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 2^nd * (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 2^nd *

 

Any help would be very much appreciated.

Thanks

James

[Funce]

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

[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

[justsomeguy]

You can get the length of a string, although you haven't said which database you're using so we don't know what's available to you.  Regardless, it looks like you have a field where you're storing multiple pieces of data, and you're figuring out the reason why it's not a good idea to do that. If the different parts of that string are each significant then they should be in their own fields.