У меня есть грязные данные в столбце с переменной длиной альфа. Я просто хочу удалить все, что не 0-9.
Я не хочу запускать функцию или процедуру. У меня есть похожий скрипт, который просто захватывает числовое значение после текста, это выглядит так:
Update TableName
set ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','') as int)
where Columnofdirtydata like 'Alpha #%'
And ColumntoUpdate is Null
Я думал, что это будет работать довольно хорошо, пока не обнаружил, что некоторые поля данных, которые я думал, будут просто в формате Alpha # 12345789 - нет.
Примеры данных, которые необходимо удалить
AB ABCDE # 123
ABCDE# 123
AB: ABC# 123
Я просто хочу 123. Это правда, что во всех полях данных есть # перед номером.
Я пробовал подстроку и PatIndex, но я не совсем понимаю синтаксис или что-то в этом роде. У кого-нибудь есть какой-нибудь совет относительно лучшего способа решения этой проблемы?
См. этот пост в блоге об извлечении чисел из строк в SQL Server. Ниже приведен пример с использованием строки в вашем примере:
DECLARE @textval NVARCHAR(30)
SET @textval = 'AB ABCDE # 123'
SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)
Вы можете использовать материал и patindex.
stuff(Col, 1, patindex('%[0-9]%', Col)-1, '')
Если между цифрами могут быть некоторые символы (например, разделители тысяч), вы можете попробовать следующее:
declare @table table (DirtyCol varchar(100))
insert into @table values
('AB ABCDE # 123')
,('ABCDE# 123')
,('AB: ABC# 123')
,('AB#')
,('AB # 1 000 000')
,('AB # 1`234`567')
,('AB # (9)(876)(543)')
;with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns),
data as (
select DirtyCol, Col
from @table
cross apply (
select (select C + ''
from (select N, substring(DirtyCol, N, 1) C from tally where N<=datalength(DirtyCol)) [1]
where C between '0' and '9'
order by N
for xml path(''))
) p (Col)
where p.Col is not NULL
)
select DirtyCol, cast(Col as int) IntCol
from data
Выход:
DirtyCol IntCol
--------------------- -------
AB ABCDE # 123 123
ABCDE# 123 123
AB: ABC# 123 123
AB # 1 000 000 1000000
AB # 1`234`567 1234567
AB # (9)(876)(543) 9876543
Для обновления добавьте ColToUpdate, чтобы выбрать список data cte:
;with num as (...),
data as (
select ColToUpdate, /*DirtyCol, */Col
from ...
)
update data
set ColToUpdate = cast(Col as int)
Это хорошо работает для меня:
CREATE FUNCTION [dbo].[StripNonNumerics]
(
@Temp varchar(255)
)
RETURNS varchar(255)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^0-9]%'
While PatIndex(@KeepValues, @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
Return @Temp
End
Затем вызовите функцию так, чтобы увидеть оригинальное что-то рядом с дезинфицированным чем-то:
SELECT Something, dbo.StripNonNumerics(Something) FROM TableA
Вот элегантное решение, если ваш сервер поддерживает функцию TRANSLATE (на сервере sql он доступен на сервере sql 2017+, а также sql azure).
Во-первых, он заменяет любые нецифровые символы символом @. Затем он удаляет все буквы @. Возможно, вам придется добавить дополнительные символы, которые, как вы знаете, могут присутствовать во втором параметре вызова TRANSLATE.
select REPLACE(TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')
Чтобы добавить к ответ Ken > , это обрабатывает запятые, пробелы и круглые скобки
--Handles parentheses, commas, spaces, hyphens..
declare @table table (c varchar(256))
insert into @table
values
('This is a test 111-222-3344'),
('Some Sample Text (111)-222-3344'),
('Hello there 111222 3344 / How are you?'),
('Hello there 111 222 3344 ? How are you?'),
('Hello there 111 222 3344. How are you?')
select
replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000) + 'X') -1),'.','')
from @table
CREATE FUNCTION FN_RemoveNonNumeric (@Input NVARCHAR(512))
RETURNS NVARCHAR(512)
AS
BEGIN
DECLARE @Trimmed NVARCHAR(512)
SELECT @Trimmed = @Input
WHILE PATINDEX('%[^0-9]%', @Trimmed) > 0
SELECT @Trimmed = REPLACE(@Trimmed, SUBSTRING(@Trimmed, PATINDEX('%[^0-9]%', @Trimmed), 1), '')
RETURN @Trimmed
END
GO
SELECT dbo.FN_RemoveNonNumeric('ABCDE# 123')
Create function fn_GetNumbersOnly(@pn varchar(100))
Returns varchar(max)
AS
BEGIN
Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0
Select @len = len(@pn)
while @x <= @len
begin
Select @c = SUBSTRING(@pn,@x,1)
if ISNUMERIC(@c) = 1 and @c <> '-'
Select @r = @r + @c
Select @x = @x +1
end
return @r
End
Declare @MainTable table(id int identity(1,1),TextField varchar(100))
INSERT INTO @MainTable (TextField)
VALUES
('6B32E')
declare @i int=1
Declare @originalWord varchar(100)=''
WHile @i<=(Select count(*) from @MainTable)
BEGIN
Select @originalWord=TextField from @MainTable where [email protected]
Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0
Select @len = len(@originalWord)
declare @pn varchar(100)[email protected]
while @x <= @len
begin
Select @c = SUBSTRING(@pn,@x,1)
if(@c!='')
BEGIN
if ISNUMERIC(@c) = 0 and @c <> '-'
BEGIN
Select @r = cast(@r as varchar) + cast(replace((SELECT ASCII(@c)-64),'-','') as varchar)
end
ELSE
BEGIN
Select @r = @r + @c
END
END
Select @x = @x +1
END
Select @r
Set @[email protected]+1
END
Здесь версия, которая вытягивает все цифры из строки; т.е. с учетом I'm 35 years old; I was born in 1982. The average family has 2.4 children., это вернет 35198224. то есть хорошо, когда у вас есть числовые данные, которые могут быть отформатированы как код (например, #123,456,789/123-00005), но не подходит, если вы хотите вытащить определенные числа (т.е. в отличие от цифр/только числовые символы) из текста. Также он обрабатывает только цифры; поэтому не будут возвращать отрицательные знаки (-) или периоды .).
declare @table table (id bigint not null identity (1,1), data nvarchar(max))
insert @table (data)
values ('hello 123 its 45613 then') --outputs: 12345613
,('1 some other string 98 example 4') --outputs: 1984
,('AB ABCDE # 123') --outputs: 123
,('ABCDE# 123') --outputs: 123
,('AB: ABC# 123') --outputs: 123
; with NonNumerics as (
select id
, data original
--the below line replaces all digits with blanks
, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(data,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') nonNumeric
from @table
)
--each iteration of the below CTE removes another non-numeric character from the original string, putting the result into the numerics column
, Numerics as (
select id
, replace(original, substring(nonNumeric,1,1), '') numerics
, replace(nonNumeric, substring(nonNumeric,1,1), '') charsToreplace
, len(replace(nonNumeric, substring(nonNumeric,1,1), '')) charsRemaining
from NonNumerics
union all
select id
, replace(numerics, substring(charsToreplace,1,1), '') numerics
, replace(charsToreplace, substring(charsToreplace,1,1), '') charsToreplace
, len(replace(charsToreplace, substring(charsToreplace,1,1), '')) charsRemaining
from Numerics
where charsRemaining > 0
)
--we select only those strings with `charsRemaining=0`; i.e. the rows for which all non-numeric characters have been removed; there should be 1 row returned for every 1 row in the original data set.
select * from Numerics where charsRemaining = 0
Этот код работает, удаляя все цифры (т.е. нужные нам символы) из данных строк, заменяя их пробелами. Затем он проходит через исходную строку (включая цифры), удаляя все оставшиеся символы (т.е. Нечисловые символы), оставляя только цифры.
Причина, по которой мы делаем это за 2 шага, а не просто удаление всех нечисловых символов, в первую очередь, есть только 10 цифр, в то время как существует огромное количество возможных символов; поэтому замена этого небольшого списка происходит относительно быстро; затем дает нам список тех нечисловых символов, которые действительно существуют в строке, поэтому мы можем заменить этот небольшой набор.
Метод использует рекурсивный SQL, используя общие табличные выражения (CTE).
Я создал функцию для этого
Create FUNCTION RemoveCharacters (@text varchar(30))
RETURNS VARCHAR(30)
AS
BEGIN
declare @index as int
declare @newtexval as varchar(30)
set @index = (select PATINDEX('%[A-Z.-/?]%', @text))
if (@index =0)
begin
return @text
end
else
begin
set @newtexval = (select STUFF ( @text , @index , 1 , '' ))
return dbo.RemoveCharacters(@newtexval)
end
return 0
END
GO
Вот ответ:
DECLARE @t TABLE (tVal VARCHAR(100))
INSERT INTO @t VALUES('123')
INSERT INTO @t VALUES('123S')
INSERT INTO @t VALUES('A123,123')
INSERT INTO @t VALUES('a123..A123')
;WITH cte (original, tVal, n)
AS
(
SELECT t.tVal AS original,
LOWER(t.tVal) AS tVal,
65 AS n
FROM @t AS t
UNION ALL
SELECT tVal AS original,
CAST(REPLACE(LOWER(tVal), LOWER(CHAR(n)), '') AS VARCHAR(100)),
n + 1
FROM cte
WHERE n <= 90
)
SELECT t1.tVal AS OldVal,
t.tval AS NewVal
FROM (
SELECT original,
tVal,
ROW_NUMBER() OVER(PARTITION BY tVal + original ORDER BY original) AS Sl
FROM cte
WHERE PATINDEX('%[a-z]%', tVal) = 0
) t
INNER JOIN @t t1
ON t.original = t1.tVal
WHERE t.sl = 1
Это сработало для меня:
Я удалил одинарные кавычки.
Затем я использовал заменить "," на "." ,
Наверняка это кому-нибудь поможет
" & txtFinalscore.Text.Replace(",", ".") & "
В вашем случае кажется, что # всегда будет после символа #, поэтому использование CHARINDEX() с LTRIM() и RTRIM(), вероятно, будет работать лучше всего. Но вот интересный способ избавиться от ЛЮБОЙ не цифры. Он использует таблицу и таблицу цифр для ограничения количества принимаемых символов, а затем технику XML для объединения обратно в одну строку без нецифровых символов. Особенность этой техники в том, что она может быть расширена за счет включения ЛЮБЫХ разрешенных символов и удаления всего, что не разрешено.
DECLARE @ExampleData AS TABLE (Col VARCHAR(100))
INSERT INTO @ExampleData (Col) VALUES ('AB ABCDE # 123'),('ABCDE# 123'),('AB: ABC# 123')
DECLARE @Digits AS TABLE (D CHAR(1))
INSERT INTO @Digits (D) VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
;WITH cteTally AS (
SELECT
I = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
@Digits d10
CROSS APPLY @Digits d100
--add more cross applies to cover longer fields this handles 100
)
SELECT *
FROM
@ExampleData e
OUTER APPLY (
SELECT CleansedPhone = CAST((
SELECT TOP 100
SUBSTRING(e.Col,t.I,1)
FROM
cteTally t
INNER JOIN @Digits d
ON SUBSTRING(e.Col,t.I,1) = d.D
WHERE
I <= LEN(e.Col)
ORDER BY
t.I
FOR XML PATH('')) AS VARCHAR(100))) o
Вы можете создать скалярную функцию SQL CLR, чтобы иметь возможность использовать регулярные выражения, такие как шаблоны замены.
Здесь вы можете найти пример того, как создать такую функцию.
Наличие такой функции решит проблему с помощью следующих строк:
SELECT [dbo].[fn_Utils_RegexReplace] ('AB ABCDE # 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('ABCDE# 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('AB: ABC# 123', '[^0-9]', '');
Что еще более важно, вы сможете решать более сложные вопросы, поскольку регулярные выражения принесут целый новый мир параметров непосредственно в ваши операторы T-SQL.