Поиск и оценка строк в SQL Server
В SQL Server я хочу оценить строку типа 1.2.30.4.50 и выводить с 0102300450 . В основном оценивайте каждое число до периода и оставляйте его пэдом с нулем, если он имеет одну цифру и выводит две цифры. Если номер уже имеет две цифры, его следует оставить нетронутым.
Строка типа 2.5.99.10.1 должна оцениваться до 0205991001 . Обратите внимание, что нет нулей до 99 и 10 .
Я знаю, что приведенный ниже запрос вернет 1-й номер до появления первого периода. Но я не в состоянии принять это дальше. Любая помощь ценится!
Если col содержит строку, скажем, 1.2.30.4.50 :
SELECT left(col, charindex('.', col) - 1) FROM table
вернет ниже:
column1 ---------- 1
Вы можете, как показано ниже
CREATE FUNCTION PrepareString ( @val varchar(max), @delimiter varchar(10) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @xml as xml SET @xml = cast(('<X>'+replace(@val,@delimiter ,'</X><X>')+'</X>') as xml) RETURN ( SELECT ( SELECT CASE WHEN LEN(N.value('.', 'varchar(MAX)')) = 1 THEN '0' + N.value('.', 'varchar(MAX)') ELSE N.value('.', 'varchar(MAX)') END FROM @xml.nodes('X') as T(N) FOR XML PATH ('') ) A ) END GO
Пример:
SELECT dbo.PrepareString('2.5.99.10.1', '.') -- 0205991001 SELECT dbo.PrepareString('1.2.30.4.50', '.') -- 0102300450 SELECT dbo.PrepareString('1.2.30.4.50.123.3.443.2', '.') -- 01023004501230344302
Моя первая мысль заключалась в использовании ParseName (), но слишком много наблюдений
Declare @String varchar(50) = '2.5.99.10.1' Select right('0'+Pos1,2)+right('0'+Pos2,2)+right('0'+Pos3,2)+right('0'+Pos4,2)+right('0'+Pos5,2) From [dbo].[udf-Str-Parse-Row](@String,'.')
Возвращает
0205991001
Параметр Row Parse UDF
CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10)) Returns Table As Return ( Select Pos1 = xDim.value('/x[1]','varchar(max)') ,Pos2 = xDim.value('/x[2]','varchar(max)') ,Pos3 = xDim.value('/x[3]','varchar(max)') ,Pos4 = xDim.value('/x[4]','varchar(max)') ,Pos5 = xDim.value('/x[5]','varchar(max)') ,Pos6 = xDim.value('/x[6]','varchar(max)') ,Pos7 = xDim.value('/x[7]','varchar(max)') ,Pos8 = xDim.value('/x[8]','varchar(max)') ,Pos9 = xDim.value('/x[9]','varchar(max)') From (Select Cast('<x>' + Replace(@String,@Delimiter,'</x><x>')+'</x>' as XML) as xDim) A ) --Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',') --Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')
Предполагая, что вы не на SQL Server 2016 и не можете использовать функцию string_split , этот тип проблемы отлично подходит для функции таблицы split split table, основанной на таблицах таблицы Tiff Table .
Чтобы создать функцию:
CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) 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 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,tN,1) = @pDelimiter ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) FROM cteStart s ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l GO
А затем объедините с for xml чтобы объединить строки в столбцы, чтобы получить результат.
-- Create some data. declare @a table(String nvarchar(100)); insert into @a values ('1.2.30.4.50') ,('06.7.80.90.10') ,('01.20.3.40.5'); -- This is how the function returns the data. select Item ,right('0' + cast(Item as varchar(10)),2) as PadItem from dbo.DelimitedSplit8K('1.2.30.4.50','.'); -- This is how you use FOR XML to concatenate the rows back together. select (select '' + right('0' + cast(Item as varchar(10)),2) from dbo.DelimitedSplit8K(a.String,'.') for xml path('') ) as Result from @aa;
Который дает Ваши fllowing результаты:
Result ---------- 0102300450 0607809010 0120034005
Это решение основано на следующем:
- Количество токенов: 5
- Минимальная длина маркера: 1
- Максимальная длина токена: 2
select right ('0' + ltrim (rtrim (substring (replace (col,'.',' '), 1,2))),2) + right ('0' + ltrim (rtrim (substring (replace (col,'.',' '), 6,3))),2) + right ('0' + ltrim (rtrim (substring (replace (col,'.',' '),11,4))),2) + right ('0' + ltrim (rtrim (substring (replace (col,'.',' '),16,5))),2) + right ('0' + ltrim (rtrim (substring (replace (col,'.',' '),21,6))),2) from tab ;
Попробуйте это ниже:
DECLARE @Str VARCHAR(100) = '2.5.99.10.1' DECLARE @EvlStr VARCHAR(100) = '',@OutPut VARCHAR(100) = ''
WHILE LEN (@Str)> 0 BEGIN
IF CHARINDEX('.',@Str) = 0 BEGIN SET @EvlStr = @Str SET @Str = '' END ELSE BEGIN SELECT @EvlStr = SUBSTRING(@Str,0,CHARINDEX('.',@Str)) SELECT @Str = SUBSTRING(@Str,CHARINDEX('.',@Str)+1,LEN(@Str)) END IF LEN(@EvlStr) = 1 SET @OutPut = @OutPut + '0' + @EvlStr ELSE SET @OutPut = @OutPut + @EvlStr
КОНЕЦ
SELECT @OutPut