SQL-запрос (по порядку)

Выберите street_name из таблицыx заказать по udf_getStreetNumber (street_name)

в вашем udf_getStreetNumber - напишите свое бизнес-правило для удаления числа

РЕДАКТИРОВАТЬ

Я думаю, что теперь вы можете использовать функцию регулярного выражения в SQL Server. Я бы просто вычеркнул из ввода все нечисловые символы.

Да, это возможно! Но определенно неинтересно! Если вы найдете здесь кого-то, готового потратить несколько часов на запись и тестирование SP, который разделит ваши streetNames на комбинацию streetName + streetNumber, сообщите мне его имя: я отправлю ему несколько задач, где, как я думал, мне пришлось заплатить, чтобы получить проделанная работа.

Кстати, нельзя ли разделить свои данные на 2 поля: одно «streetName» только с названием улицы и новое поле «buildingNumber»? (Не называйте это «streetNumber», так как в некоторых странах / городах улицам даются номера).

Я уверен, что вы могли бы разделить поле streetName на разные части с помощью чего-то вроде substr (streetName, 1, find ("", streetName)) только для улицы и так далее. Но это будет довольно беспорядочно, и ему придется иметь дело со всеми видами особых случаев (без номера дома, номер дома без добавления) или международными проблемами (в США адреса обычно похожи на 1 Street).

Но если вы хотите, чтобы сортировка выполнялась так, как вы описали, и это является важным требованием, было бы лучше смоделировать ваше streetName в трех частях, то есть улице (например, «Улица»), house_number (например, 1, 2, 12), house_num_addition ( например, «А», «Б»). Тогда сортировка в SQL становится тривиальной.

Если у вас есть доступ для записи в базу данных, я бы действительно рекомендовал преобразовать все это, чтобы использовать 3 отдельных поля, а затем использовать их соответствующим образом. Таким образом, вы могли бы сделать это даже в PHP (да, это займет некоторое время, но это произойдет только один раз). Это может быть неприятно, если у вас большая база кода, и вам нужно проверять все запросы с помощью этой таблицы, но в конечном итоге это окупится позже. Например, это значительно упростит поиск по адресу.

Надежный способ сделать это (надежный с точки зрения «правильной сортировки данных», а не «решения вашей общей проблемы») - разделить данные на название улицы и номер дома и сортировать их по отдельности. Но для этого нужно знать, где начинается номер дома. И это сложная часть - сделать предположение, которое лучше всего соответствует вашим данным.

Вы должны использовать что-то вроде следующего для рефакторинга ваших данных и с этого момента хранить номер дома в отдельном поле. Когда дело доходит до сортировки больших наборов данных, все эти манипуляции со строками не очень эффективны.

Предположим, что это последнее слово в названии улицы, и оно содержит номер:

DECLARE @test TABLE
(
  street VARCHAR(100)
)

INSERT INTO @test (street) VALUES('Street')
INSERT INTO @test (street) VALUES('Street 1A')
INSERT INTO @test (street) VALUES('Street1 12B')
INSERT INTO @test (street) VALUES('Street 22A')
INSERT INTO @test (street) VALUES('Street1 200B-8a')
INSERT INTO @test (street) VALUES('')
INSERT INTO @test (street) VALUES(NULL)

SELECT
  street,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN LEFT(street, LEN(street) - CHARINDEX(' ', REVERSE(street)))
    END
  END street_part,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE 
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1)
    END
  END house_part,
  CASE 
    WHEN LEN(street) > 0 AND CHARINDEX(' ', REVERSE(street)) > 0
    THEN CASE 
      WHEN RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1) LIKE '%[0-9]%'
      THEN CASE
        WHEN PATINDEX('%[a-z]%', LOWER(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1))) > 0
        THEN CONVERT(INT, LEFT(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1), PATINDEX('%[^0-9]%', LOWER(RIGHT(street, CHARINDEX(' ', REVERSE(street)) - 1))) - 1))
      END
    END
  END house_part_num
FROM
  @test 
ORDER BY
  street_part,
  house_part_num,
  house_part

Это предполагает следующие условия:

• почтовый адрес может имеет номер дома
• номер дома должен должен быть последним в почтовом адресе (не "525 Monroe Av.")
• номер дома должен начинается с цифры для правильной сортировки
• номер дома может будет диапазоном ("200-205"), это будет отсортировано ниже 200
• номер дома не должен содержит пробелы или распознавание не выполняется (когда вы смотрите на свои данные, вы можете применить что-то вроде REPLACE(street, ' - ', '-') для предварительной очистки общих шаблонов).
• все это по-прежнему является приближением, которое определенно отличается от того, как это могло бы выглядеть в телефонной книге, например

Если все значения в столбце streetNames соответствуют шаблону StreetName- пробел - StreetNumber

где StreetName может содержать другие пробелы, но StreetNumber НЕ МОЖЕТ, тогда это будет работать:

Declare @T Table (streetName VarChar(50))
Insert @T(streetName) Values('Street 1A')
Insert @T(streetName) Values('Street 2A')
Insert @T(streetName) Values('Street 2B')
Insert @T(streetName) Values('Street 12A')
Insert @T(streetName) Values('Another Street 1A')
Insert @T(streetName) Values('Another Street 4A')
Insert @T(streetName) Values('a third Street 12B')
Insert @T(streetName) Values('a third Street 1C')

Select * From @T 
Order By Substring(StreetName, 0, 1 + len(StreetName) - charIndex(' ', reverse(StreetName))),
       Cast(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)),  
        Case When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 5)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 4)) = 1  Then 4
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 3)) = 1  Then 3
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 2)) = 1  Then 2
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 1)) = 1  Then 1
                End) as Integer),
        Substring(StreetName, len(StreetName) - charIndex(' ', reverse(StreetName)) +
            Case When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 5)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 4)) = 1  Then 6
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 3)) = 1  Then 5
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 2)) = 1  Then 4
             When IsNumeric(Substring(StreetName, 2 + len(StreetName) - charIndex(' ', reverse(StreetName)), 1)) = 1  Then 3
                End, Len(StreetName))

Для записи: он называется Естественный порядок сортировки, а есть Кодирующая статья ужасов в теме.

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