• <tfoot id='1fuZB'></tfoot>

    1. <legend id='1fuZB'><style id='1fuZB'><dir id='1fuZB'><q id='1fuZB'></q></dir></style></legend>

      1. <small id='1fuZB'></small><noframes id='1fuZB'>

          <bdo id='1fuZB'></bdo><ul id='1fuZB'></ul>

        <i id='1fuZB'><tr id='1fuZB'><dt id='1fuZB'><q id='1fuZB'><span id='1fuZB'><b id='1fuZB'><form id='1fuZB'><ins id='1fuZB'></ins><ul id='1fuZB'></ul><sub id='1fuZB'></sub></form><legend id='1fuZB'></legend><bdo id='1fuZB'><pre id='1fuZB'><center id='1fuZB'></center></pre></bdo></b><th id='1fuZB'></th></span></q></dt></tr></i><div id='1fuZB'><tfoot id='1fuZB'></tfoot><dl id='1fuZB'><fieldset id='1fuZB'></fieldset></dl></div>
      2. SQL Server 相当于 MySQL 中的 substring_index 函数

        时间:2023-06-01
      3. <small id='8JFJr'></small><noframes id='8JFJr'>

          <legend id='8JFJr'><style id='8JFJr'><dir id='8JFJr'><q id='8JFJr'></q></dir></style></legend>

                <tbody id='8JFJr'></tbody>
              <tfoot id='8JFJr'></tfoot>
              • <bdo id='8JFJr'></bdo><ul id='8JFJr'></ul>

                <i id='8JFJr'><tr id='8JFJr'><dt id='8JFJr'><q id='8JFJr'><span id='8JFJr'><b id='8JFJr'><form id='8JFJr'><ins id='8JFJr'></ins><ul id='8JFJr'></ul><sub id='8JFJr'></sub></form><legend id='8JFJr'></legend><bdo id='8JFJr'><pre id='8JFJr'><center id='8JFJr'></center></pre></bdo></b><th id='8JFJr'></th></span></q></dt></tr></i><div id='8JFJr'><tfoot id='8JFJr'></tfoot><dl id='8JFJr'><fieldset id='8JFJr'></fieldset></dl></div>

                  本文介绍了SQL Server 相当于 MySQL 中的 substring_index 函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我正在尝试将查询从 MySQL 移植到 SQL SERVER 2012.

                  我如何为 MySQL 的 substring_index() 编写等价物?

                  I am trying to port a query from MySQL to SQL SERVER 2012.

                  How do i write an equivalent for MySQL's substring_index()?

                  MySQL SUBSTRING_INDEX() 返回给定字符串之前的子字符串指定的分隔符出现次数.

                  MySQL SUBSTRING_INDEX() returns the substring from the given string before a specified number of occurrences of a delimiter.

                  SUBSTRING_INDEX(str, delim, count)

                  SUBSTRING_INDEX(str, delim, count)

                  SELECT SUBSTRING_INDEX('www.somewebsite.com','.',2);
                  

                  输出:'www.somewebsite'

                  推荐答案

                  试试这个基于 T-SQL 和 XQuery 的解决方案((root/row)[position() <= sql:variable("@count")]):

                  Try this solution based on T-SQL and XQuery((root/row)[position() <= sql:variable("@count")]):

                  T-SQL 标量函数:

                  CREATE FUNCTION dbo.SUBSTRING_INDEX
                  (
                      @str NVARCHAR(4000),
                      @delim NVARCHAR(1),
                      @count INT
                  )
                  RETURNS NVARCHAR(4000)
                  WITH SCHEMABINDING
                  BEGIN
                      DECLARE @XmlSourceString XML;
                      SET @XmlSourceString = (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>');
                  
                      RETURN STUFF
                      (
                          ((
                              SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'
                              FROM    @XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)
                              FOR XML PATH(N''), TYPE
                          ).value(N'.', N'NVARCHAR(4000)')), 
                          1, 1, N''
                      );
                  END
                  GO
                  
                  SELECT dbo.SUBSTRING_INDEX(N'www.somewebsite.com', N'.', 2) AS Result;
                  

                  输出:

                  /*
                  Result
                  ---------------
                  www.somewebsite
                  */
                  

                  TSQL 内联表值函数:

                  CREATE FUNCTION dbo.SUBSTRING_INDEX
                  (
                      @str NVARCHAR(4000),
                      @delim NVARCHAR(1),
                      @count INT
                  )
                  RETURNS TABLE
                  AS 
                  RETURN
                      WITH Base
                      AS 
                      (
                          SELECT XmlSourceString = CONVERT(XML, (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>'))
                      )   
                      SELECT STUFF
                      (
                          ((
                              SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'
                              FROM    Base b 
                              CROSS APPLY b.XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)
                              FOR XML PATH(N''), TYPE
                          ).value(N'.', N'NVARCHAR(4000)')), 
                          1, 1, N''
                      ) AS Result;
                  GO
                  
                  SELECT  *
                  FROM    (
                      SELECT N'www.somewebsite.com' UNION ALL 
                      SELECT N'www.yahoo.com' UNION ALL 
                      SELECT N'www.outlook.com'
                  ) a(Value)
                  CROSS APPLY dbo.SUBSTRING_INDEX(a.Value, N'.', 2) b;
                  

                  输出:

                  /*
                  Value               Result
                  ------------------- ---------------
                  www.somewebsite.com www.somewebsite
                  www.yahoo.com       www.yahoo
                  www.outlook.com     www.outlook
                  */
                  

                  这篇关于SQL Server 相当于 MySQL 中的 substring_index 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:MySQL #1140 - GROUP 列的混合 下一篇:MySQL INT 含义

                  相关文章

                • <i id='SepYM'><tr id='SepYM'><dt id='SepYM'><q id='SepYM'><span id='SepYM'><b id='SepYM'><form id='SepYM'><ins id='SepYM'></ins><ul id='SepYM'></ul><sub id='SepYM'></sub></form><legend id='SepYM'></legend><bdo id='SepYM'><pre id='SepYM'><center id='SepYM'></center></pre></bdo></b><th id='SepYM'></th></span></q></dt></tr></i><div id='SepYM'><tfoot id='SepYM'></tfoot><dl id='SepYM'><fieldset id='SepYM'></fieldset></dl></div>
                  <legend id='SepYM'><style id='SepYM'><dir id='SepYM'><q id='SepYM'></q></dir></style></legend>

                  <small id='SepYM'></small><noframes id='SepYM'>

                    <tfoot id='SepYM'></tfoot>
                      <bdo id='SepYM'></bdo><ul id='SepYM'></ul>