sql Check if column is substring of another column

substring sql
check if value in one column exists in another column sql
sql check if string contains letters
sql-like another column
if column contains value then sql
compare a column with another column in sql
sql substring after character
case when contains sql

I am trying to check one column substring is in another columns

table1

FullName              
Johns Doue               
Johnny                   
Betty Smith, Chair     

table2

Name       
John

using table2 to see if it is a substring of table1. it should return Johns Doue and Johnny.

SELECT * FROM table1.FullName AS table1      
JOIN table2.Name AS table2    
WHERE table2.Name LIKE SUBSTRING(table1.FullName, 0, 10);

this is returning null's being compared. im not sure what i am doing wrong. From my logic, it seems like its taking the results from table2.name and comparing to the substrings of table1.FullName.

You need to put wildcards in the LIKE pattern to make it look for substrings. You also seem to be confusing table and column names in your SELECT syntax.

SELECT *
FROM table1 AS t1
JOIN table2 AS t2
WHERE t1.FullName LIKE CONCAT('%', t2.Name, '%')

You can also use LOCATE instead of LIKE

SELECT *
FROM table1 AS t1
JOIN table2 AS t2
WHERE LOCATE(t2.Name, t1.FullName) > 0

DEMO

general - Check if column is substring of another column, Check if column is substring of another column. PostgreSQL Is there a way to do this in a single SQL statement in PostgreSQL 8.1? Thanks, Check If Column Is Substring Of Another Column May 29, 2008. I have two columns. policyNumber contains a 12-13 varchar string AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas

Check if column is substring of another column, Posted - 2008-05-29 : 17:26:22. I have two columns. policyNumber contains a 12 -13 varchar string AllPolicyNumbersIncluded contains one or more 12-13� I want to compare the value stored in the policyNumber column to see if it is all or psrt of the value in AllPolicyNumbersIncluded column. Peso's query returned only if the value in AllPolicyNumbersIncluded column was the same as the value stored in the policyNumber. column.

LOCATE is not available in some SQL flavours (e.g. Postgres) and apparently using string concatenation to build a SQL query is frowned upon (source: TypeError: 'dict' object does not support indexing).

You could alternatively use the POSITION command:

SELECT *
FROM table1 AS t1
JOIN table2 AS t2
WHERE POSITION(t2.Name IN t1.FullName) > 0

Check if column is substring of another column, I want to check if policyNumber is contained in AllPolicyNumbersIncluded. In SQL Server the PATINDEX function returns the starting position of� select count (value) as count from table 1 join table 2 on substring (value,20,10)=substring (code,0,10) where reference='1'. this will work for 2nd query, but it is not way to do it. You are manually giving length and index of each substring. – ubaid ashraf Feb 6 '15 at 6:46. add a comment. |.

How To Check If A String Contains A Substring In SQL Server, SQL contains string is used to check if a string contains a substring in SQL In this blog, we saw how to get data with substrings in a column. The LIKE predicate operator can be used to find a substring into a string or content. The LIKE operator combined with % and _ (underscore) is used to look for one more more characters and a single character respectively. You can use % operator to find a sub string. In the following SQL query, we will look for a substring, 'Kumar" in the string.

SQL Server SUBSTRING Function By Practical Examples, The SUBSTRING() function raises an error if the length is negative. If start + length > the B) Using SUBSTRING() function with table columns. We will use the Then, we used the result of the CHARINDEX() function to determine: The starting� e.g. MyTable.[FullName] (existing column) contains a string like "P-13-146 PS - Goodyear - Tire repair" I created a new column, MyTable.[ShortName] that I want to insert a substring of the value from the first column into ("P-13-146 PS"). The problem is that the length of the substring is different for each full name.

Checking if a Cell Contains a Substring in Excel or Google Docs, Learn how to check if a cell contains a substring in Excel or Google Docs with the trusty If you've ever been in a situation where you're working with (yet another) new columns where we'll perform our SEARCH function above to see if that value SQL may be the language of data, but not everyone can understand it. Re: Check if column is substring of another column at 2009-01-22 05:11:48 from Rodrigo E. De León Plicet Re: Check if column is substring of another column at 2009-01-22 07:49:52 from Richard Huxton Browse pgsql-general by date

Comments
  • i did the first query but the t2.name resulting a null
  • I added a demo showing that both queries work with your sample data.
  • I have already done that but its still giving me a results of an empty string thats compared to another empty string.
  • or there is something populated in the t1.fullname but its being compared with t2.name and t2.name is null. fullname john doue name (empty)
  • @user2113896 -- guessing here a little, but what about just adding where criteria to eliminate blank values? sqlfiddle.com/#!9/290973/2
  • yeah i added a statement where it checks if its not null but doesnt work.
  • @user2113896 -- at this point, you need to clarify your question... did you try the working fiddle? can you replicate the issue you are having in it and repost the link to the one that doesn't work correctly?