久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

SQL Server 2014 替換為正則表達(dá)式

SQL Server 2014 replace with regex(SQL Server 2014 替換為正則表達(dá)式)
本文介紹了SQL Server 2014 替換為正則表達(dá)式的處理方法,對(duì)大家解決問(wèn)題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧!

問(wèn)題描述

替換列中字符串的最佳方法是什么:

假設(shè)我有一個(gè)列類型為 varchar 的表,可能的值包含:

'sample text min(my value) 用 ) 和 ('

注意:我的價(jià)值可能會(huì)有所不同.

現(xiàn)在我想將其替換為:

min(max(my value))

所以在這種情況下的最終值是:

'sample text min(max(my value)) 用 ) 和 ('

我想對(duì)整個(gè)表執(zhí)行更新.

是否可以使用純 T-SQL?

所以 => 之前和轉(zhuǎn)換之后的兩個(gè)示例行:

<代碼>1.值為:min(10).=>值為: min(max(10))2. 樣本 min(cat) =>樣本最小值(最大值(貓))

基本上用min(max(value))替換所有出現(xiàn)的min(value),其中'value'可以是任何字符串

解決方案

首先你需要這個(gè)用戶定義的函數(shù)來(lái)搜索用字符串替換模式:

CREATE FUNCTION dbo.PatternReplace(@InputString VARCHAR(4000),@Pattern VARCHAR(100),@ReplaceText VARCHAR(4000))返回 VARCHAR(4000)作為開(kāi)始聲明 @Result VARCHAR(4000) SET @Result = ''-- 匹配中的第一個(gè)字符聲明@First INT-- 下一個(gè)開(kāi)始搜索的字符聲明 @Next INT SET @Next = 1-- 總字符串的長(zhǎng)度 -- 如果@InputString 為 NULL,則為 8001聲明@Len INT SET @Len = COALESCE(LEN(@InputString), 8001)-- 模式結(jié)束聲明 @EndPattern INT而 (@Next <= @Len)開(kāi)始SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))IF COALESCE(@First, 0) = 0 -- 不匹配 - 返回開(kāi)始SET @Result = @Result +CASE --return NULL, 就像 REPLACE, 如果輸入是 NULL當(dāng)@InputString 為空時(shí)或 @Pattern 為空或 @ReplaceText 為 NULL THEN NULLELSE SUBSTRING(@InputString, @Next, @Len)結(jié)尾休息結(jié)尾別的開(kāi)始-- 將匹配之前的字符連接到結(jié)果SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)SET @Next = @Next + @First - 1設(shè)置@EndPattern = 1-- 查找結(jié)束模式范圍的開(kāi)始而 PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0設(shè)置@EndPattern = @EndPattern + 1-- 查找模式范圍的結(jié)尾而 PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) >0AND @Len >= (@Next + @EndPattern - 1)SET @EndPattern = @EndPattern + 1-- 在模式末尾或@Next + @EndPattern = @LenSET @Result = @Result + @ReplaceTextSET @Next = @Next + @EndPattern - 1結(jié)尾結(jié)尾返回(@結(jié)果)結(jié)尾

閱讀更多

min(xxx) 出現(xiàn)不止一次:

最后,您可以簡(jiǎn)單地更新您的表格,如下所示:

更新你的表SET YourColumn = REPLACE(dbo.PatternReplace(YourColumn, '%min(', 'min(max('),'min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ ')','min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ '))');

What is the best way to replace string in column like:

Let's say I have table with column type varchar and with the possible value that contains:

'sample text min(my value) continue with sample text with ) and ('

Note: my value can vary.

Now I would like to replace it with:

min(max(my value))

so that the final value would in that case be:

'sample text min(max(my value)) continue with sample text with ) and ('

I would like to perform update on the whole table.

Is it possible using pure T-SQL?

So two sample rows before => and after transformation:

1. the value is: min(10). => the value is: min(max(10))
2. sample min(cat) => sample min(max(cat)) 

Basically replace all occurrences of min(value) with min(max(value)) where 'value' can be any string

解決方案

First of all you need this user defined function to search for replacing a pattern with string:

CREATE FUNCTION dbo.PatternReplace
(
   @InputString VARCHAR(4000),
   @Pattern VARCHAR(100),
   @ReplaceText VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
   DECLARE @Result VARCHAR(4000) SET @Result = ''
   -- First character in a match
   DECLARE @First INT
    -- Next character to start search on
    DECLARE @Next INT SET @Next = 1
    -- Length of the total string -- 8001 if @InputString is NULL
    DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString), 8001)
    -- End of a pattern
    DECLARE @EndPattern INT

     WHILE (@Next <= @Len) 
     BEGIN
     SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))
      IF COALESCE(@First, 0) = 0 --no match - return
       BEGIN
          SET @Result = @Result + 
             CASE --return NULL, just like REPLACE, if inputs are NULL
                WHEN  @InputString IS NULL
                 OR @Pattern IS NULL
                 OR @ReplaceText IS NULL THEN NULL
           ELSE SUBSTRING(@InputString, @Next, @Len)
        END
     BREAK
  END
  ELSE
  BEGIN
     -- Concatenate characters before the match to the result
     SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)
     SET @Next = @Next + @First - 1

     SET @EndPattern = 1
     -- Find start of end pattern range
     WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0
        SET @EndPattern = @EndPattern + 1
     -- Find end of pattern range
     WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) > 0
           AND @Len >= (@Next + @EndPattern - 1)
        SET @EndPattern = @EndPattern + 1

     --Either at the end of the pattern or @Next + @EndPattern = @Len
     SET @Result = @Result + @ReplaceText
     SET @Next = @Next + @EndPattern - 1
  END
      END
      RETURN(@Result)
   END

Read more here

After creating this function you can try this:

DECLARE @x VARCHAR(max)
SET @x = 'sample text min(my value) continue with sample text with ) and ('

DECLARE @val VARCHAR(max)

SET @val =  SUBSTRING(SUBSTRING(@x,CHARINDEX('min(',@x)+4,LEN(@x)-CHARINDEX('min(',@x)),1,CHARINDEX(')',@x)-(CHARINDEX('min(',@x)+4))

SELECT REPLACE(dbo.PatternReplace(@x,'%min(','min(max('),'min(max('+@val+')','min(max('+@val+'))')

And you can see that the output is:

Occurrence of min(xxx) more than once:

Finally you can simply update your table as below:

UPDATE  YourTable
SET     YourColumn = REPLACE(dbo.PatternReplace(YourColumn, '%min(', 'min(max('),
                   'min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ ')',
                   'min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ '))');

這篇關(guān)于SQL Server 2014 替換為正則表達(dá)式的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!

【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題,如果有圖片或者內(nèi)容侵犯了您的權(quán)益,請(qǐng)聯(lián)系我們刪除處理,感謝您的支持!

相關(guān)文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個(gè)子標(biāo)記轉(zhuǎn)換為具有多個(gè)分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個(gè)表創(chuàng)建視圖?)
Create calculated value based on calculated value inside previous row(根據(jù)前一行內(nèi)的計(jì)算值創(chuàng)建計(jì)算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對(duì)?) - IT屋-程序員軟件開(kāi)發(fā)技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉(zhuǎn)換為日期/月份編號(hào)(問(wèn)題和答案的組合))
主站蜘蛛池模板: 国产特级毛片aaaaaa | 精品久久久久久亚洲精品 | 国产精品一区二区视频 | 狠狠狠干 | 久久国产精品99久久久大便 | 欧美一区二区三区在线 | 日本三级网 | 欧美日本韩国一区二区 | 午夜精品一区二区三区在线观看 | 午夜激情免费视频 | 中文字幕在线三区 | 国产一区二区在线视频 | 99精品久久99久久久久 | www久久久| 国产区一区二区三区 | 国产精品视频久久 | 国产传媒在线播放 | 欧美一区二区三区在线视频 | 久久亚洲美女 | h视频在线观看免费 | 亚洲一区二区不卡在线观看 | 亚洲午夜小视频 | 亚洲区一区二区 | 国产精品久久久久久久久久久久久 | 精品免费国产一区二区三区 | 色婷婷狠狠 | 久久久久久免费观看 | 精品国产一区二区三区性色av | 91在线影院 | 国产资源在线视频 | 国产高清在线 | 亚洲高清视频在线观看 | 91精品在线看 | 在线观看国产视频 | 成人一级毛片 | 国产在线一区二区 | 日韩2020狼一二三 | av在线免费看网址 | 日韩一级 | 国产成人精品一区二区三区网站观看 | 国产99久久精品 |