Добре дошли!

Добре дошли в нашите форуми, пълни с полезна информация. Имате проблем с компютъра или телефона си? Публикувайте нова тема и ще намерите решение на всичките си проблеми. Общувайте свободно и открийте безброй нови приятели.

Моля, регистрирайте се за да публикувате тема и да получите пълен достъп до всички функции.

Регулярен израз

28 март 201313 г.

Здравейте,

на .NET съм написала следното:

 namespace RegExpression{    public static class RegExp    {	    [sqlFunction(IsDeterministic=true, IsPrecise=true)]	    public static int RegularExpression(string inputValue, string regExp)	    {		    try		    {			    if (inputValue == null)				    return 0;			    if (Regex.IsMatch(inputValue, regExp))				    return 1;			    else				    return 0;		    }		    catch (Exception)		    {			    throw;		    }	    }    }}

когато проверявам даден израз от .NET всичко си е наред. Проблема идва, когато ползвам този .dll при създаване на асембли в MSSQL Server.

Създавам си асемблито и функцията:

USE [master]GOCREATE ASSEMBLY [RegExpr]AUTHORIZATION [dbo]FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001C6354510000000000000000E00002210B010800000A00000006000000000000AE2800000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000005828000053000000004000002803000000000000000000000000000000000000006000000C000000B02700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B408000000200000000A000000020000000000000000000000000000200000602E72737263000000280300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000090280000000000004800000002000500C0200000F006000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3002002E0000000100001100000214FE0116FE010B072D04160ADE1A0203281000000A16FE010B072D04170ADE08160ADE042600FE1A00062A000001100000000001002627000414000001133002001700000002000011000203281200000A6F1300000A281400000A0A2B00062A0042534A4201000100000000000C00000076322E302E35303732370000000005006C00000048020000237E0000B40200003403000023537472696E677300000000E80500000800000023555300F005000010000000234755494400000000060000F000000023426C6F620000000000000002000001471502000900000000FA2533001600000100000017000000020000000200000004000000140000000E00000002000000010000000300000000000A00010000000000060041003A000A007B0066000600C300B1000600E000B1000600FD00B10006001C01B10006003501B10006004E01B10006006901B10006008401B1000600BC019D010600D0019D010600DE01B1000600F701B1000600270214023F003B02000006006A024A0206008A024A020E00C702A8020600D5023A000A00FA02DF020E000F03A8020E001503A8020000000001000000000001000100810110001C002300050001000100502000000000960048000A0001009C20000000009600860010000300000001009100000002009C0000000100A30000000200A9001900DA0017002100DA0017002900DA0017003100DA0017003900DA0017004100DA0017004900DA0017005100DA0017005900DA001C006100DA0017006900DA0017007100DA0017007900DA0021008900DA0027009100DA002C009900CD023000A900DA002C0099000F034000B9001B034700110027034B002E0033006F002E000B0056002E00130069002E001B0069002E00230069002E002B0056002E00530087002E003B0069002E004B0069002E006B00BE002E007B00D0002E006300B1002E007300C70040008B003B003600510004800000010000000000000000000000000023000000020000000000000000000000010031000000000002000000000000000000000001005A000000000002000000000000000000000001003A00000000000000003C4D6F64756C653E0052656745787072657373696F6E2E646C6C005265674578700052656745787072657373696F6E006D73636F726C69620053797374656D004F626A65637400526567756C617245787072657373696F6E0053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0052656765784D6174636800696E70757456616C75650072656745787000696E707574007061747465726E0053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780049734D6174636800457863657074696F6E004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465004D617463680047726F7570006765745F53756363657373006F705F496D706C6963697400000003200000000000328569CEC18F894FB7A343F24C6456250008B77A5C561934E089050002080E0E06000211090E0E042001010E0420010102052001011141042001010803200001050002020E0E0407020802040100000006000212590E0E0320000205000111090204070111091201000D52656745787072657373696F6E000005010000000017010012436F7079726967687420C2A920203230313300002901002432353736616635372D373766342D343963342D623834332D30353238383462373062666500000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000001C635451000000000200000089000000CC270000CC090000525344535E0C645939F8D74E8EE47A7E0C6FA2F701000000433A5C55736572735C652E6D617267696E615C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C52656745787072657373696F6E5C52656745787072657373696F6E5C6F626A5C44656275675C52656745787072657373696F6E2E706462000000008028000000000000000000009E2800000020000000000000000000000000000000000000000000009028000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E0000000000520065006700450078007000720065007300730069006F006E000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000044001200010049006E007400650072006E0061006C004E0061006D0065000000520065006700450078007000720065007300730069006F006E002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100330000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000520065006700450078007000720065007300730069006F006E002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D00650000000000520065006700450078007000720065007300730069006F006E000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000WITH PERMISSION_SET = UNSAFEGOCREATE FUNCTION [dbo].[RegularExpression](@InputValue [nvarchar](max), @RegExp [nvarchar](max))returns intEXTERNAL NAME [RegExpr].[RegExpression.RegExp].[RegularExpression]GO

при проверка на по-малък израз всичко е ОК.

Примерно:

 declare @Number nvarchar(100), @RegExp nvarchar(50) select @Number = '-12-1234'  select @Number = UPPER(ltrim(rtrim(@Number))) select @RegExp = '^-(([d]{2})-([d]{4}))$|^([d]{9})$|' SELECT dbo.[RegularExpression](@Number, @RegExp) AS value;

проблемът идва САМО при извикването на екстърнал функцията от MSSQL Server - a (когато я викам тази проверка от .NET е наред), когато регулярният израз е по-голям

Примерно:

 declare @Number nvarchar(100), @RegExp nvarchar(50) select @Number = '-12-1234'  select @Number = UPPER(ltrim(rtrim(@Number))) select @RegExp = '^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35d{3})d{11})$' SELECT dbo.[RegularExpression](@Number, @RegExp) AS value;

Грешката, която връща е следната:

Msg 6522, Level 16, State 1, Line 16 A .NET Framework error occurred during execution of user-defined routine or aggregate "RegexMatch": System.ArgumentException: parsing "^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:01" - Not enough )'s. System.ArgumentException: at System.Text.RegularExpressions.RegexParser.ScanRegex() at System.Text.RegularExpressions.RegexParser.Parse(String re, RegexOptions op) at System.Text.RegularExpressions.Regex..ctor(String pattern, RegexOptions options, Boolean useCache) at System.Text.RegularExpressions.Regex.Match(String input, String pattern) at RegExpression.RegExp.RegexMatch(String input, String pattern) .

Някой има ли идея къде бъркам?

28 март 201313 г.

Автор

Здравейте,

сега виждам че, при изпълнение на функцията

  select @RegExp = '^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35d{3})d{11})  SELECT dbo.[RegularExpression](@Number, @RegExp) AS value;

"НЕ" отива целият израз, а само част от него ( ^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:01 ) и следователно в .НЕТ се случва грешката, че регулярният израз не е валиден... Въпросът ми сега е защо при изпълнение на екстърнал процедурата в .НЕТ отива само част от регулярният израз, а не целия, при положение, че типа на @RegExp е nvarchar(max).

Къде бъркам?

28 март 201313 г.

а не целия, при положение, че типа на @RegExp е nvarchar(max).

По-горе виждам

@RegExp nvarchar(50)

Точно 50 символа е взело

...

28 март 201313 г.

Автор

По-горе виждам
@RegExp nvarchar(50)
Точно 50 символа е взело ...

Ето дефиницията на функцията:

CREATE FUNCTION [dbo].[RegularExpression](@InputValue [nvarchar](max), @RegExp [nvarchar](max))returns intEXTERNAL NAME [RegExpr].[RegExpression.RegExp].[RegularExpression]GO

и ако изпълня

select @RegExp = '^123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890$'SELECT dbo.[RegularExpression](@Number, @RegExp) AS value;

си е нараред... Обаче сега виждам, че наистина ми взима само част от дългият стринг и дължината наистина е 50. Прав си, но защо приема само nvarchar(50), като съм му дала nvarchar(max)? П. П. В метода в .НЕТ си направих да ми записва стойностите на параметрите в един текстов файл и видях, че наистина е само 50...

Редактирано 28 март 201313 г. от helena 2 3 4 5 1 (преглед на промените)

28 март 201313 г.

Ето дефиницията на функцията: си е нараред... Обаче сега виждам, че наистина ми взима само част от дългият стринг и дължината наистина е 50. Прав си, но защо приема само nvarchar(50), като съм му дала nvarchar(max)?

Не параметъра на функцията. Самата променлива си я декларирала да е 50 символа:

declare @Number nvarchar(100), @RegExp nvarchar(50)

29 март 201313 г.

Автор

Не параметъра на функцията. Самата променлива си я декларирала да е 50 символа:
declare @Number nvarchar(100), @RegExp nvarchar(50)

Да, ... Хич не съм догледала, ужас ... Благодаря много : )))

Архивирана тема

Темата е твърде стара и е архивирана. Не можете да добавяте нови отговори в нея, но винаги можете да публикувате нова тема, в която да продължи дискусията. Регистрирайте се или влезте във вашия профил за да публикувате нова тема.