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, Nov 10 '17 at 06:59
So the question here is what are we trying to achieve? Is it a code sample to display My Name is Meenakshi — Satya, Nov 10 '17 at 07: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, Nov 10 '17 at 07: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, Nov 10 '17 at 07:08
@E.Villiger , i am looking same thing in SQL, i wrote java code to understand what exactly i want in SQL — Prade jo, Nov 10 '17 at 07:12
I don't think SQL is the right tool for this. Sounds more like it should be a PHP question. — E. Villiger, Nov 10 '17 at 07:17

score 0 · Answer 1 · answered Nov 10 '17 at 07:23

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.

score 0 · Answer 2 · answered Nov 10 '17 at 07:30

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 '17 at 07:30

Jayasurya Satheesh

7,826
3
22
39

your answer looking like split a string in sub string ; its very far far from my question. – Prade jo Nov 13 '17 at 04:16
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 – Jayasurya Satheesh Nov 13 '17 at 04:29

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

2 Answers2

Linked