help linking two tables in a view

[Mike Bishop]

I am trying to create a view and by linking two tables together, VLP_QC_Results_Batch1000 and VLP_Stock_QC1000. (see below pics)

The issue I have is in the VLP_QC_Results_Batch1000 has the testNo within a field called TestNoName and is separated by a “-“
I need to link these so that we can show the upper and lower limit for each test and the test results

I am unsure if i can link on the BatchID and the QCID as I don’t know how these tables interact with each other.

Is there a simple way to get all the characters before the “-“ from the TestNoName field? I could trim it but the TestNo is different sizes.

[debasisdas]

The issue I have is in the VLP_QC_Results_Batch1000 has the testNo within a field called TestNoName and is separated by a “-“
I need to link these so that we can show the upper and lower limit for each test and the test results

I really did not understand this block.

[Mike Bishop]

i am trying to link VLP_QC_Results to VLP_Stock_QC1000

on VLP_QC_Results has a feild called testnoname which has something like s2-TotalSolids which i would like to link with VLP_Stock_QC1000 where the fild has s2

[debasisdas]

Is it the case that data will be always consistent as per format in that column ?

[Mike Bishop]

yes always (testno)-(something)i need a way to get the data before the first "-"

[debasisdas]

If the length and format is always consistent, you can manipulate the same using some basic string functions SUBSTRING.

[Mike Bishop]

do you have any examples of the substring?

[debasisdas]

Read here and here.

[Mike Bishop]

thanks for the links but this doesn't tell me how to get the data upto the "-"

[BitBlt]

You can use the CHARINDEX function to locate the first occurrence of the character in question. Then use that number in the substring to get the length you need.

Use this sample code to construct queries against your own data:

select myString,
charindex('-', myString, 1) as 'Here is the position of dash',
substring(myString, 1, charindex('-', myString, 1)) as 'Here is the substring with dash',
substring(myString, 1, charindex('-', myString, 1) - 1) as 'Here is the substring without dash'
from dbo.myTable

Hope this helps!