0

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.

Attachments Picture1.jpg 47.86 KB Picture2.jpg 64.57 KB
3
Contributors
9
Replies
10
Views
6 Years
Discussion Span
Last Post by BitBlt
0

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.

0

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

0

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

1

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!

Votes + Comments
that is exactly what i had suggested to the member in another thread.
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.