compair 2 strings in sql [first string contain 2nd string's substrings]

Question

i am sharing a java code related to my question, by that you can easily understand what i am exactly looking in SQL.

Java code

String s1 = "http://hdvidz.co/video/file/Naa-Peru-Meenakshi-%7C-11th?id=rj5e--8vQb4";
    String s2 = "Naa Peru Meenakshi";
    String splitStringS2[] = s2.split(" ");// using blank space to split
    int i = 0;
    for (String a : splitStringS2) {
        if (s1.contains(a)) {
            i = i + 1;
        } else {
            System.out.println("break perform");
            i = 0;
            break;
        }
    }
    System.out.println("value of i===  " + i);

i have 2 table ; table A has a column "link" that contain value "http://hdvidz.co/video/file/Naa-Peru-Meenakshi-%7C-11th?id=rj5e--8vQb4"; table B has a column "name" that contain value "Naa Peru Meenakshi"

CREATE TABLE `A` (`link` VARCHAR(255) );
insert into A values("http://hdvidz.co/video/file/Naa-Peru-Meenakshi-%7C-11th?id=rj5e--8vQb4");
CREATE TABLE `B`(`name` VARCHAR(255) );
insert into B values("Naa Peru Meenakshi");

now what exactly i want

1) pick a value from table B and split into substring and store in array splitName.

2) pick a value from table A & store in variable url

3) now checking substring (splitName) exist in variable url

4) if all substring found in url return count (substring match) , else return 0

above java code is doing same thing.

choose any one of MySQL, java & sql-server which are different. — Yogesh Sharma
– Yogesh Sharma, Commented Nov 10, 2017 at 6:59
So the question here is what are we trying to achieve? Is it a code sample to display My Name is Meenakshi — Satya
– Satya, Commented Nov 10, 2017 at 7:01
Your question is not clear at all. Please be specific and detailed about what you're trying to do in which language. — E. Villiger
– E. Villiger, Commented Nov 10, 2017 at 7:07
@Satya i have lot of data in two different tables, one table contain String s1 and second table contain string s2, and we want to check 1st string belonging to 2 string or not , this is a example for single test here we will perform it with multiple data — Prade jo
– Prade jo, Commented Nov 10, 2017 at 7:08
@E.Villiger , i am looking same thing in SQL, i wrote java code to understand what exactly i want in SQL — Prade jo
– Prade jo, Commented Nov 10, 2017 at 7:12

bitnahian · Accepted Answer · 2017-11-10 07:23:48Z

0

You can write a stored procedure. I haven't used MySQL in a while, but I'm fairly sure it doesn't support a split function. You can write a user-defined procedure as done in here.

I think you can also use LOCATE to find the position of a sub-string inside a string. UPDATE: Check the second answer in the link.

answered Nov 10, 2017 at 7:23

bitnahian

5966 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jayasurya Satheesh · Accepted Answer · 2017-11-10 07:30:15Z

0

For MS SQL Server Try this below query :

WITH CTE
AS
(
 SELECT
  1 as Seq  ,
  COL ,
  Val = COL
  FROM T1
  UNION ALL
  SELECT
    Seq = Seq+1,
  COL = LTRIM(SUBSTRING(COL,CHARINDEX(' ',COL),LEN(COL))),
  Val = LTRIM(RTRIM(SUBSTRING(COL,1,CHARINDEX(' ',COL))))
  FROM CTE
    WHERE ISNULL(Val,'')<>''
)
SELECT
  Val = CASE WHEN ISNULL(Val,'')<>'' THEN Val
    ELSE Col END
  FROM CTE
    WHERE Seq > 1

I'm assuming that you have these values in a column COL on table T1. You can replace the first select query

SELECT
  1 as Seq  ,
  COL ,
  Val = COL
  FROM T1

With your desired input.

For the below input

The output is as below

Check The SQL Fiddle here

answered Nov 10, 2017 at 7:30

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

2 Comments

Prade jo Over a year ago

your answer looking like split a string in sub string ; its very far far from my question.

Jayasurya Satheesh Over a year ago

Then Please add some more details to the question. You have only mentioned that "you can easily identify my requirement" from the code and this is what I understood

Collectives™ on Stack Overflow

compair 2 strings in sql [first string contain 2nd string's substrings]

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related