Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 как сортировать строку например '1.42.345'  [new]
PG81
Member

Откуда: Тверь
Сообщений: 882
есть текстовое поле в котором пишутся номера этапов работв следующем виде
например
'1.3.2'
'1.3.3'
'1.3.11'
Простое ORDER BY не помогает, так как это строка
необходимо привести наверное к следующему виду 000100030002 и т..д., только как не знаю.
23 май 11, 16:20    [10695398]     Ответить | Цитировать Сообщить модератору
 Re: как сортировать строку например '1.42.345'  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
PG81,

SELECT PARSENAME('1.3.2',3),PARSENAME('1.3.2',2),PARSENAME('1.3.2',1)
23 май 11, 16:22    [10695414]     Ответить | Цитировать Сообщить модератору
 Re: как сортировать строку например '1.42.345'  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
select '000'+REPLACE('1.2.3', '.', '000')
23 май 11, 16:24    [10695436]     Ответить | Цитировать Сообщить модератору
 Re: как сортировать строку например '1.42.345'  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
PG81,

лучше во время UPDATEа, "в котором пишутся номера", формировать служебное поле,
выровненное специально для сортировки. А именно:
  • положение точек должно быть одинаково во всех сортируемых строках;
  • самое лучшее точки вообще не использовать, а добавлять лидирующие нули до постоянного количества символов.
    Например, 10 символов годится для любого неотрицательного целого числа.
    К примеру для каждого числа
    REPLACE(STR(IntNumber,10),' ','0')
    Получится '000000000100000000420000000345'

    Иначе придётся каждый раз парсить строки, добавлять лидирующие нули или пробелы - зачем?
  • 23 май 11, 16:38    [10695555]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    Можно в поле вместо строки писать hierarchyid
    Тогда оно сортируется нормально. А в запросе выдавать через ToString()
    SELECT	Replace(Stuff(T.[Этап].ToString(),1,1),'/','.') AS [Этап]
    FROM	...
    ORDER BY T.[Этап]
    Хотя может и извращение. Зато легко следующий этап указывать, или начинать подэтап:
    SELECT	 [Этап].GetAncestor(1).GetDescendant([Этап],NULL)	-- Следующий этап
    	,[Этап].GetDescendant(NULL,NULL)			-- Начинаем первый под-этап
    
    Если подаётся извне строка можно её за-Parse-ить.

    Ну а если лень и хотите оставить это извращение и грузить проц тупыми задачами можно и так оставить:
    ORDER BY hierarchyid::Parse('/' + Replace(T.[Этап],'.','/'))
    23 май 11, 21:18    [10696898]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    PG81
    Member

    Откуда: Тверь
    Сообщений: 882
    kDnZP
    не подходит PARSENAME, так как количество частей указания номера этапа может быть от одной до бесконечности, например,
    1
    1.1
    1.2
    1.2.1
    1.2.1.1
    1.2.2 и т.д. в таком варианте сортируется неправильно

    Maxx
    select '000'+REPLACE('1.2.3', '.', '000')
    совершенно не подходит, так как числа частей могут быть многозначные

    Mnior
    hierarchyid не подходит, так как ситема старая, она не понимает такой тип поля, и эти же значения вводит пользователь и там могут быть не только числа но и буквы
    24 май 11, 09:05    [10697970]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47142
    PG81
    kDnZP
    не подходит PARSENAME, так как количество частей указания номера этапа может быть от одной до бесконечности, например,
    1
    1.1
    1.2
    1.2.1
    1.2.1.1
    1.2.2 и т.д. в таком варианте сортируется неправильно

    Maxx
    select '000'+REPLACE('1.2.3', '.', '000')
    совершенно не подходит, так как числа частей могут быть многозначные

    Mnior
    hierarchyid не подходит, так как ситема старая, она не понимает такой тип поля, и эти же значения вводит пользователь и там могут быть не только числа но и буквы
    Так Вы можете постоянно поддерживать поле для сортировки в нормализованном виде?
    Если б >=SQL2005, то можно было бы рекурсивное CTE при выборке замутить.
    Но для сортировки-то всё равно в этом CTE пришлось бы формировать эту самую строку с путём. Стандартный приём, так сказать.
    24 май 11, 09:52    [10698186]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    PG81,

    можно попробовать использовать xml для парсинга строки
    declare @t table(s varchar(100))
    insert into @t values ('1.3.2'),('1.3.3'),('1.3.11'),('1.3.11.1'),('1.3.2.1'),('1.2.1')
    
    select 
    	t.s,
    	sort.ex
    from 
    	@t t
    	 -- формируем xml
    	cross apply ( select convert( xml, '<root><a>'+replace(t.s,'.','</a><a>')+'</a></root>' ) ) x(a)
    	-- формиоруем строку для сортировки: парсим xml на узлы, выравниваем значения и снова склеиваем в строку
    	cross apply ( select replace(str(n.a.value('.','int'),5),' ','0') from x.a.nodes('root/a') n(a) for xml path('') ) sort(ex)
    order by
    	sort.ex
    (особо не проверял)
    но, имхо, каждый раз затевать кантиель с парсингом не айс, я бы на вашем месте прислушался к совету iap, относительно служебного поля для сортировки...
    24 май 11, 10:17    [10698324]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    PG81
    Member

    Откуда: Тверь
    Сообщений: 882
    написал функцию, зацените. Вроде работает и все сортирует нормально.
    Только вот возникли небольшие нюансы. Оказалось что у этапов могут быть буквенные обозначения
    например, '1.13а.1' или '1.5.4б', прчем букв может быть не одна а несколько '1.15.7абв' .В этом случае сортировка не работает. Что посоветуете? Может буквы совсем вырезать? или отдельно их также как и числа добавлять символами?

    CREATE FUNCTION [dbo].[nom_to_str](@Str varchar(max))  
    RETURNS varchar(max)  AS  
    BEGIN 
      declare @Res varchar(max)
      declare @Tmp varchar(max)
      declare @RestStr varchar(max)
      declare @PointPos bigint	 
    set @Res = ''
    set @RestStr = @Str 
    set @PointPos = patindex('%.%',@RestStr) 
    while 1=1
    begin
      if (@PointPos>0)
      begin
        set @Tmp = substring(@RestStr,1,@PointPos-1)  
        set @RestStr = substring(@RestStr,@PointPos+1,100)
      end
      else
        set @Tmp = substring(@RestStr,1,100)
      set @Tmp = replicate('0',10-len(@Tmp))+@Tmp 
      set @Res = @Res+@Tmp
      if (@PointPos=0) break
      set @PointPos = patindex('%.%',@RestStr) 
    end
      return @Res
    END
    
    
    25 май 11, 09:34    [10703958]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    Владимир СА
    Member

    Откуда:
    Сообщений: 7915
    PG81
    А справочник этапов как-то на клиенте формируется?
    25 май 11, 09:39    [10703993]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    kDnZP
    Member [заблокирован]

    Откуда: ★[msg=16399436]★[msg=20850760]
    Сообщений: 11289
    PG81, уже посоветовали - формировать служебное поле при инсертах/апдейтах. Иначе быть беде с производительностью.
    25 май 11, 09:41    [10704016]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    PG81
    Member

    Откуда: Тверь
    Сообщений: 882
    Владимир СА,

    не понял вопроса.
    Есть работы которые выполняются в несколько этапов. Эти этапы вводят вручную пользователи, когда заносят сведения по работам.
    Сортировака на клиенте не происходт, а записи сразу должны выдаваться в правильном порядке.Поэтому сортировка асуществляется на сервере.
    25 май 11, 09:42    [10704018]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    Anatoly Podgoretsky
    Member

    Откуда:
    Сообщений: 62908
    PG81
    написал функцию, зацените. Вроде работает и все сортирует нормально.
    Только вот возникли небольшие нюансы. Оказалось что у этапов могут быть буквенные обозначения
    например, '1.13а.1' или '1.5.4б', прчем букв может быть не одна а несколько '1.15.7абв' .В этом случае сортировка не работает. Что посоветуете? Может буквы совсем вырезать? или отдельно их также как и числа добавлять символами?

    Сортировка работает, кроме того тут нет ничего кроме букв, ведь строка '1.5.4б' это не число или дата, это символы. И никакой возможности превратить это в число не удастся, так что алфавитные символы сортируются точно также как и алфавитно-цифровые.
    25 май 11, 09:42    [10704021]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    PG81
    Member

    Откуда: Тверь
    Сообщений: 882
    kDnZP,

    с производительностью ничего не будет, так как количество пользователей ограничено максимум 5 человек, а то и 1-2.
    и это не единственна ятаблица с которой работают и не самая главная, так что все будет нормально. Нет, понятно, если делать правильно то нужно делать служебное поле, но ведь эту функцию можно использовать в тригере для формирования этого служебного поля при изменении значения
    25 май 11, 09:45    [10704041]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    PG81
    Member

    Откуда: Тверь
    Сообщений: 882
    Anatoly Podgoretsky,

    а никто и не собирается превращать строку символов в число, ее нужно просто разделить на равные блоки и тогда сортировака будет проходить нормально
    25 май 11, 09:47    [10704058]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    Владимир СА
    Member

    Откуда:
    Сообщений: 7915
    PG81
    Владимир СА,

    не понял вопроса.
    Есть работы которые выполняются в несколько этапов. Эти этапы вводят вручную пользователи, когда заносят сведения по работам.
    Сортировака на клиенте не происходт, а записи сразу должны выдаваться в правильном порядке.Поэтому сортировка асуществляется на сервере.
    Т.к. этапы вводят пользователи, то по моему его (как бы справочник этапов) надо было формировать в древовидной структуре и пользователи добавляли этапы/подэтапы в эту структуру (видя куда это вводится). Ну теперь с помощью СТЕ выводить эту древовидную структуру...
    25 май 11, 09:48    [10704062]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    Anatoly Podgoretsky
    Member

    Откуда:
    Сообщений: 62908
    PG81
    Anatoly Podgoretsky,

    а никто и не собирается превращать строку символов в число, ее нужно просто разделить на равные блоки и тогда сортировака будет проходить нормально

    Ну так раздели, ведь проблемы с сортировкой нет. Только как определить размер этих блоков?
    25 май 11, 09:59    [10704130]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    kDnZP
    Member [заблокирован]

    Откуда: ★[msg=16399436]★[msg=20850760]
    Сообщений: 11289
    PG81, не... ну можно конечно разбирать/собирать строки, даже с учетом символов, вот как пример (с того, что выше, чуть переработан). Но это не совсем верный путь ИМХО.
    DECLARE @t TABLE ( s VARCHAR(100) )
    INSERT  INTO @t
            SELECT  '1.3.2'
            UNION ALL
            SELECT  '1.3.3'
            UNION ALL
            SELECT  '1.3.3a'
            UNION ALL
            SELECT  '1.3.3b'
            UNION ALL
            SELECT  '1.3.11'
            UNION ALL
            SELECT  '1.3.11.1'
            UNION ALL
            SELECT  '1.3.2.1'
            UNION ALL
            SELECT  '1.2.1'
    
    SELECT  t.s,
            sort.ex
    FROM    @t t -- формируем xml
    CROSS APPLY ( SELECT    CONVERT(XML, '<root><a>' + REPLACE(t.s, '.', '</a><a>') + '</a></root>')
                ) x ( a )
    	-- формиоруем строку для сортировки: парсим xml на узлы, выравниваем значения и снова склеиваем в строку
    CROSS APPLY ( SELECT    CASE WHEN PATINDEX('%[^a-z]%',RIGHT(n.a.value('.', 'varchar(10)'),1))<>0 THEN RIGHT('000000' + n.a.value('.', 'varchar(10)'),6)
    ELSE RIGHT('000000' + LEFT(n.a.value('.', 'varchar(10)'),LEN(n.a.value('.', 'varchar(10)'))-1)+'_'+RIGHT(n.a.value('.', 'varchar(10)'),1),8) END
                  FROM      x.a.nodes('root/a') n ( a )
                FOR
                  XML PATH('')
                ) sort ( ex )
    ORDER BY sort.ex
    
    25 май 11, 10:12    [10704211]     Ответить | Цитировать Сообщить модератору
     Re: как сортировать строку например '1.42.345'  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    PG81
    с производительностью ничего не будет, так как количество пользователей ограничено максимум 5 человек, а то и 1-2
    1-5 человек в грузит словно там их 1000.


    Правильно, сначало надо проектировать, а потом думать и извращаться в 735% кода.
    25 май 11, 11:17    [10704846]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить