Ik wil elke markering tussen twee delen van het nummer vervangen door bovenstaande regex, maar het lijkt niet te werken. Ik weet niet zeker of het de regex-syntaxis is die verkeerd is, want ik heb een eenvoudigere geprobeerd, zoals '%[^0-9]%', alleen om te testen, maar het werkte ook niet. Weet iemand hoe ik dit kan bereiken?
Antwoord 1, autoriteit 100%
U kunt PATINDEXgebruiken
om de eerste index van het patroon (tekenreeks) te vinden. Gebruik vervolgens STUFFom een andere tekenreeks in het overeenkomende patroon(tekenreeks) te stoppen.
Loop door elke rij. Vervang elk illegaal karakter door wat je wilt. Vervang in uw geval niet-numeriek door spatie. De binnenste lus is als je meer dan één ongeldig teken in een huidige cel hebt, die van de lus.
DECLARE @counter int
SET @counter = 0
WHILE(@counter < (SELECT MAX(ID_COLUMN) FROM Table))
BEGIN
WHILE 1 = 1
BEGIN
DECLARE @RetVal varchar(50)
SET @RetVal = (SELECT Column = STUFF(Column, PATINDEX('%[^0-9.]%', Column),1, '')
FROM Table
WHERE ID_COLUMN = @counter)
IF(@RetVal IS NOT NULL)
UPDATE Table SET
Column = @RetVal
WHERE ID_COLUMN = @counter
ELSE
break
END
SET @counter = @counter + 1
END
Let op: dit gaat echter langzaam! Het hebben van een varchar-kolom kan van invloed zijn. Dus het gebruik van LTRIM RTRIM kan een beetje helpen. Hoe dan ook, het is traag.
Krediet gaat naar ditStackOverFlow-antwoord.
BEWERKEN
Krediet gaat ook naar @srutzky
Bewerken (door @tmdean)
In plaats van een rij tegelijkertijd te doen, kan dit antwoord worden aangepast aan een meer ingestelde oplossing. Het toont nog steeds de max van het aantal niet-numerieke tekens in een enkele rij, dus het is niet ideaal, maar ik denk dat het in de meeste situaties acceptabel moet zijn.
WHILE 1 = 1 BEGIN
WITH q AS
(SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n
FROM Table)
UPDATE Table
SET Column = STUFF(Column, q.n, 1, '')
FROM q
WHERE Table.ID_Column = q.ID_Column AND q.n != 0;
IF @@ROWCOUNT = 0 BREAK;
END;
U kunt ook de efficiëntie vrij veel verbeteren als u een bitkolom in de tabel behoudt die aangeeft of het veld is geschrobd. (Null vertegenwoordigt “onbekend” in mijn voorbeeld en moet de kolominformatie zijn.)
DECLARE @done bit = 0;
WHILE @done = 0 BEGIN
WITH q AS
(SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n
FROM Table
WHERE COALESCE(Scrubbed_Column, 0) = 0)
UPDATE Table
SET Column = STUFF(Column, q.n, 1, ''),
Scrubbed_Column = 0
FROM q
WHERE Table.ID_Column = q.ID_Column AND q.n != 0;
IF @@ROWCOUNT = 0 SET @done = 1;
-- if Scrubbed_Column is still NULL, then the PATINDEX
-- must have given 0
UPDATE table
SET Scrubbed_Column = CASE
WHEN Scrubbed_Column IS NULL THEN 1
ELSE NULLIF(Scrubbed_Column, 0)
END;
END;
Als u uw schema niet wilt wijzigen, is dit eenvoudig aan te passen aan het opslaan van intermediaire resultaten in een variabele met de gewaardeerde tabel die aan het einde op de werkelijke tabel wordt toegepast.
Antwoord 2, Autoriteit 44%
In plaats van het gevonden teken te verwijderen op zijn enige positie, zou het gebruik van Replace(Column, BadFoundCharacter, '')aanzienlijk sneller kunnen zijn. Bovendien, in plaats van alleen het ene slechte teken dat hierna in elke kolom wordt gevonden te vervangen, vervangt dit alle gevonden tekens.
WHILE 1 = 1 BEGIN
UPDATE dbo.YourTable
SET Column = Replace(Column, Substring(Column, PatIndex('%[^0-9.-]%', Column), 1), '')
WHERE Column LIKE '%[^0-9.-]%'
If @@RowCount = 0 BREAK;
END;
Ik ben ervan overtuigd dat dit beter zal werken dan het geaccepteerde antwoord, al was het maar omdat het minder bewerkingen uitvoert. Er zijn andere manieren die misschien ook sneller zijn, maar ik heb nu geen tijd om die te verkennen.
Antwoord 3, autoriteit 38%
In algemene zin ondersteunt SQL Server geen reguliere expressies en kunt u ze niet gebruiken in de native T-SQL-code.
Je zou een CLR-functie kunnen schrijven om dat te doen. Zie bijvoorbeeld hier.
Antwoord 4, autoriteit 8%
Voor wie op zoek is naar een performante en gemakkelijke oplossing en bereid is CLR in te schakelen:
create database TestSQLFunctions
go
use TestSQLFunctions
go
alter database TestSQLFunctions set trustworthy on
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
go
CREATE ASSEMBLY [SQLFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004BE8B85F0000000000000000E00022200B013000000800000006000000000000C2270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000702700004F000000004000009803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C8070000002000000008000000020000000000000000000000000000200000602E72737263000000980300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000A4270000000000004800000002000500682000000807000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003A022D02022A020304281000000A2A1E02281100000A2A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000018020000237E000084020000CC02000023537472696E6773000000005005000004000000235553005405000010000000234755494400000064050000A401000023426C6F620000000000000002000001471500000900000000FA0133001600000100000013000000020000000200000003000000110000000F00000001000000030000000000CA01010000000000060025014F02060092014F02060044001D020F006F02000006006C00E20106000801E2010600D400E20106007901E20106004501E20106005E01E20106008300E2010600580030020600360030020600B700E20106009E00B0010600AA02DB010A00F300FC010A001F00FC010E00C3027E02000000000100000000000100010001001000C3029D0241000100010050200000000096002E001A0001005F2000000000861817020600040000000100BD0200000200F40100000300B102090017020100110017020600190017020A0029001702100031001702100039001702100041001702100049001702100051001702100059001702100061001702150069001702100071001702100079001702100089001702060099002E001A0081001702060020007B0010012E000B002A002E00130033002E001B0052002E0023005B002E002B006D002E0033006D002E003B006D002E0043005B002E004B0073002E0053006D002E005B006D002E0063008B002E006B00B5002E007300C2000480000001000000000000000000000000009D020000040000000000000000000000210016000000000004000000000000000000000021000A00000000000400000000000000000000002100DB010000000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200446174614163636573734B696E64005265706C61636500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E56657273696F6E696E670053514C46756E6374696F6E732E646C6C0053797374656D0053797374656D2E5265666C656374696F6E007061747465726E004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E546578742E526567756C617245787072657373696F6E730053514C46756E6374696F6E73004F626A656374007265706C6163656D656E7400696E70757400526567657800000000000000003A1617E607071B47B964858BCD87458B00042001010803200001052001011111042001010E04200101020600030E0E0E0E08B77A5C561934E0890801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001101000C53514C46756E6374696F6E73000005010000000017010012436F7079726967687420C2A920203230323000002901002434346436386231632D393735312D343938612D396665352D32316666333934303738303900000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E352E320100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E352E32808F010001005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000000000982700000000000000000000B2270000002000000000000000000000000000000000000000000000A4270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000003C03000000000000000000003C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0049C020000010053007400720069006E006700460069006C00650049006E0066006F0000007802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000042000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C00460075006E006300740069006F006E00730000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000042001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C00460075006E006300740069006F006E0073002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003200300000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000004A00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C00460075006E006300740069006F006E0073002E0064006C006C00000000003A000D000100500072006F0064007500630074004E0061006D00650000000000530051004C00460075006E006300740069006F006E00730000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C43700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
go
CREATE FUNCTION RegexReplace(
@input nvarchar(max),
@pattern nvarchar(max),
@replacement nvarchar(max)
) RETURNS nvarchar (max)
AS EXTERNAL NAME SQLFunctions.[SQLFunctions.Regex].Replace;
go
-- outputs This is a test
select dbo.RegexReplace('This is a test 12345','[0-9]','')
Inhoud van de DLL:
Antwoord 5, autoriteit 6%
Ik kwam dit bericht tegen toen ik op zoek was naar iets anders, maar ik dacht dat ik een oplossing zou noemen die ik gebruik die veel efficiënter is – en die eigenlijk de standaardimplementatie zou moeten zijn van elke functie bij gebruik met een op een set gebaseerde query – die moet worden gebruikt een kruis toegepaste tabelfunctie. Het lijkt erop dat het onderwerp nog steeds actief is, dus hopelijk is dit nuttig voor iemand.
Voorbeeld van runtime op een paar van de antwoorden tot nu toe gebaseerd op het uitvoeren van recursieve set-gebaseerde query’s of scalaire functie, gebaseerd op 1m rijen testset die de tekens verwijdert uit een willekeurige newid, varieert van 34s tot 2m05s voor de WHILE-lusvoorbeelden en van 1m3s tot {forever} voor de functievoorbeelden.
Het gebruik van een tabelfunctie met kruiselings toepassen bereikt hetzelfde doel in 10s. Mogelijk moet u het aanpassen aan uw behoeften, zoals de maximale lengte die het aankan.
Functie:
CREATE FUNCTION [dbo].[RemoveChars](@InputUnit VARCHAR(40))
RETURNS TABLE
AS
RETURN
(
WITH Numbers_prep(Number) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,Numbers(Number) AS
(
SELECT TOP (ISNULL(LEN(@InputUnit),0))
row_number() OVER (ORDER BY (SELECT NULL))
FROM Numbers_prep a
CROSS JOIN Numbers_prep b
)
SELECT
OutputUnit
FROM
(
SELECT
substring(@InputUnit,Number,1)
FROM Numbers
WHERE substring(@InputUnit,Number,1) like '%[0-9]%'
ORDER BY Number
FOR XML PATH('')
) Sub(OutputUnit)
)
Gebruik:
UPDATE t
SET column = o.OutputUnit
FROM ##t t
CROSS APPLY [dbo].[RemoveChars](t.column) o
Antwoord 6, autoriteit 6%
Hier is een functie die ik heb geschreven om dit te bereiken op basis van de eerdere antwoorden.
CREATE FUNCTION dbo.RepetitiveReplace
(
@P_String VARCHAR(MAX),
@P_Pattern VARCHAR(MAX),
@P_ReplaceString VARCHAR(MAX),
@P_ReplaceLength INT = 1
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Index INT;
-- Get starting point of pattern
SET @Index = PATINDEX(@P_Pattern, @P_String);
while @Index > 0
begin
--replace matching charactger at index
SET @P_String = STUFF(@P_String, PATINDEX(@P_Pattern, @P_String), @P_ReplaceLength, @P_ReplaceString);
SET @Index = PATINDEX(@P_Pattern, @P_String);
end
RETURN @P_String;
END;
Bewerken:
Oorspronkelijk had ik hier een recursieve functie die niet goed werkt met de sql-server, omdat deze een limiet van 32 nesting-niveaus heeft, wat zou resulteren in een fout zoals hieronder wanneer u probeert om 32+ vervangingen met de functie te maken. In plaats van te proberen een wijziging op serverniveau aan te brengen om meer nesten toe te staan (wat gevaarlijk kan zijn, zoals het toestaan van oneindige lussen), is het veel logischer om over te schakelen naar een while-lus.
Maximaal opgeslagen procedure-, functie-, trigger- of weergavenestniveau overschreden (limiet 32).
Antwoord 7, autoriteit 3%
Het kan handig zijn om de oplossing in een SQL-functie te verpakken als u deze opnieuw wilt gebruiken.
Ik doe het zelfs op celniveau, daarom plaats ik dit als een ander antwoord:
CREATE FUNCTION [dbo].[fnReplaceInvalidChars] (@string VARCHAR(300))
RETURNS VARCHAR(300)
BEGIN
DECLARE @str VARCHAR(300) = @string;
DECLARE @Pattern VARCHAR (20) = '%[^a-zA-Z0-9]%';
DECLARE @Len INT;
SELECT @Len = LEN(@String);
WHILE @Len > 0
BEGIN
SET @Len = @Len - 1;
IF (PATINDEX(@Pattern,@str) > 0)
BEGIN
SELECT @str = STUFF(@str, PATINDEX(@Pattern,@str),1,'');
END
ELSE
BEGIN
BREAK;
END
END
RETURN @str
END
Antwoord 8, autoriteit 3%
Ik heb deze functie gemaakt om een tekenreeks op te schonen die niet-numerieke tekens in een tijdveld bevat. De tijd bevatte vraagtekens wanneer ze de minuten niet hebben toegevoegd, zoiets als deze 20:??. Functie loopt door elk teken en vervangt de ? met een 0 :
CREATE FUNCTION [dbo].[CleanTime]
(
-- Add the parameters for the function here
@intime nvarchar(10)
)
RETURNS nvarchar(5)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar nvarchar(5)
DECLARE @char char(1)
-- Add the T-SQL statements to compute the return value here
DECLARE @i int = 1
WHILE @i <= LEN(@intime)
BEGIN
SELECT @char = CASE WHEN substring(@intime,@i,1) like '%[0-9:]%' THEN substring(@intime,@i,1) ELSE '0' END
SELECT @ResultVar = concat(@ResultVar,@char)
set @i = @i + 1
END;
-- Return the result of the function
RETURN @ResultVar
END
Antwoord 9, Autoriteit 3%
Ik denk dat deze oplossing sneller en eenvoudig is. Ik gebruik altijd CTE / Recursive Baguus terwijl het zo traag is op MSSQL.
Ik gebruik het in projecten die ik werk met en grote databases.
/*
Function: dbo.kSql_ReplaceRegExp
Create Date: 20.02.2021
Author: Karcan Ozbal
Description: The given string value will be replaced according to the given regexp/pattern.
Parameter(s): @Value : Value/Text to REPLACE.
@RegExp : The regexp/pattern to be used for REPLACE operation.
Usage: select dbo.kSql_ReplaceRegExp('2T3EST5','%[0-9]%')
Output: 'TEST'
*/
ALTER FUNCTION [dbo].[kSql_ReplaceRegExp](
@Value nvarchar(max),
@RegExp nvarchar(50)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @Result nvarchar(max)
;WITH CTE AS (
SELECT NUM = 1, VALUE = @Value, IDX = PATINDEX(@RegExp, @Value)
UNION ALL
SELECT NUM + 1, VALUE = REPLACE(VALUE, SUBSTRING(VALUE,IDX,1),''), IDX = PATINDEX(@RegExp, REPLACE(VALUE, SUBSTRING(VALUE,IDX,1),''))
FROM CTE
WHERE IDX > 0
)
SELECT TOP(1) @Result = VALUE
FROM CTE
ORDER BY NUM DESC
OPTION (maxrecursion 0)
RETURN @Result
END
Antwoord 10, Autoriteit 2%
Als u dit alleen doet voor een parameter die in een opgeslagen procedure komt, kunt u het volgende gebruiken:
declare @badIndex int
set @badIndex = PatIndex('%[^0-9]%', @Param)
while @badIndex > 0
set @Param = Replace(@Param, Substring(@Param, @badIndex, 1), '')
set @badIndex = PatIndex('%[^0-9]%', @Param)
Antwoord 11
Ik denk dat een eenvoudiger en snellere aanpak wordt geïntegreerd door elk karakter van het alfabet:
DECLARE @i int
SET @i = 0
WHILE(@i < 256)
BEGIN
IF char(@i) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.')
UPDATE Table SET Column = replace(Column, char(@i), '')
SET @i = @i + 1
END
Antwoord 12
Ik dacht dat dit duidelijker was:
ALTER FUNCTION [dbo].[func_ReplaceChars](
@Value nvarchar(max),
@Chars nvarchar(50)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @cLen int = len(@Chars);
DECLARE @curChar int = 0;
WHILE @curChar<@cLen
BEGIN
set @Value = replace(@Value,substring(@Chars,@curChar,1),'');
set @curChar = @curChar + 1;
END;
RETURN @Value
END