Search code examples
sqlsql-servert-sqlsql-likestring-length

Return length of string for comparison

Is it possible to compare two strings on the length of the second string for example.

SELECT ProductDescription, Products  
FROM ProductDescription
INNER JOIN Products 
ON ProductDescription LIKE LEFT(Products, LENGTH(Products)) + '%'

Where Products is the string we are comparing with and the length of the comparison I would like to be the same length as the string if that makes sense?

Say we have Hydrogen and Hydrogen Oxide Like matches both so I need to specify what I am comparing...

further more the Description fields may for instance state:

Hydrogen Oxide is water. Oxygen is air. Hydrogen II and Oxygen II can be combined to create water. Hydrogen is a gas. Oxygen II is undefined.

I have a list of states for example: Oxygen. Hydrogen. Oxygen II. Hydrogen II. Hydrogen Oxide.

I would like to append the Correct chemicals to the Descriptions they concern. for example:

Hydrogen Oxide is water. | Hydrogen Oxide. Oxygen is air. | Oxygen Hydrogen II and Oxygen II can be combined to create water. | Hydrogen II, Oxygen II Hydrogen is a gas. | Hydrogen Oxygen II is undefined. | Oxygen II.

The results I can get from the query vary but I am trying to avoid:

Hydrogen Oxide is water. | Hydrogen Oxide. Oxygen is air. | Oxygen. Hydrogen II and Oxygen II can be combined to create water. | Oxygen II. Hydrogen II and Oxygen II can be combined to create water. | Hydrogen. II Hydrogen is a gas. | Hydrogen Oxygen II is undefined. | Oxygen.

Solution

  • Looking back at this question the Answer to this question is actually very simple... The Queries suggested by the guys here at Stack Overflow and the code I had written myself, in fact did exactly as it was supposed to... That being said the data sets were very messy causing some invalid results to be returned. This can only be corrected in one of two ways, one way being cleaning the data set through string manipulation. The other method to be obtain a clean data set of which you can compare implicitly with the clean data set obtained which is the method I had taken to bypass the issue of invalid data... I hope this helps someone...