比较sql中的2个字符串[第一个字符串包含第二个字符串的子字符串] [英] compair 2 strings in sql [first string contain 2nd string's substrings]

问题描述

我正在分享与我的问题相关的 Java 代码，这样您就可以轻松了解我在 SQL 中的确切内容.

Java 代码

String s1 = "http://hdvidz.co/video/file/Naa-Peru-Meenakshi-%7C-11th?id=rj5e--8vQb4";String s2 = "Naa Peru Meenakshi";String splitStringS2[] = s2.split(" ");//使用空格分割int i = 0;for (String a : splitStringS2) {如果(s1.contains(a)){i = i + 1;} 别的 {System.out.println("中断执行");我 = 0;休息;}}System.out.println("i===的值" + i);

我有 2 张桌子；表 A 有一列链接"，其中包含值

输出如下

在此处查看 SQL Fiddle

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.

解决方案

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

这篇关于比较sql中的2个字符串[第一个字符串包含第二个字符串的子字符串]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！