반응형
문자열에서 숫자만 가져오기 위한 쿼리
다음과 같은 데이터가 있습니다.
string 1: 003Preliminary Examination Plan
string 2: Coordination005
string 3: Balance1000sheet
제가 기대하는 출력은
string 1: 003
string 2: 005
string 3: 1000
SQL에 구현하고 싶습니다.
먼저 작성
CREATE FUNCTION dbo.udf_GetNumeric
(
@strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
이제 를 로서 사용합니다.
SELECT dbo.udf_GetNumeric(column_name)
from table_name
이것으로 당신의 문제가 해결되었기를 바랍니다.
이거 먹어봐-
쿼리:
DECLARE @temp TABLE
(
string NVARCHAR(50)
)
INSERT INTO @temp (string)
VALUES
('003Preliminary Examination Plan'),
('Coordination005'),
('Balance1000sheet')
SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1)
FROM (
SELECT subsrt = SUBSTRING(string, pos, LEN(string))
FROM (
SELECT string, pos = PATINDEX('%[0-9]%', string)
FROM @temp
) d
) t
출력:
----------
003
005
1000
쿼리:
DECLARE @temp TABLE
(
string NVARCHAR(50)
)
INSERT INTO @temp (string)
VALUES
('003Preliminary Examination Plan'),
('Coordination005'),
('Balance1000sheet')
SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%',
string) + 1) AS Number
FROM @temp
시험해 보세요:
declare @var nvarchar(max)='Balance1000sheet'
SELECT LEFT(Val,PATINDEX('%[^0-9]%', Val+'a')-1) from(
SELECT SUBSTRING(@var, PATINDEX('%[0-9]%', @var), LEN(@var)) Val
)x
한 줄에서 숫자만 얻을 수 있습니다.다음을 시도해 보십시오.
SUBSTRING('your-string-here', PATINDEX('%[0-9]%', 'your-string-here'), LEN('your-string-here'))
NB: 문자열의 첫 번째 int에서만 작동합니다.예: abc123vfg34는 123을 반환합니다.
이 접근방식은 투표율이 가장 높은 답변보다 약 3배 빠른 것으로 나타났습니다.다음 함수 dbo를 만듭니다.Get Numbers:
CREATE FUNCTION dbo.GetNumbers(@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN;
WITH
Numbers
AS (
--Step 1.
--Get a column of numbers to represent
--every character position in the @String.
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < LEN(@String)
)
,Characters
AS (
SELECT Character
FROM Numbers
CROSS APPLY (
--Step 2.
--Use the column of numbers generated above
--to tell substring which character to extract.
SELECT SUBSTRING(@String, Number, 1) AS Character
) AS c
)
--Step 3.
--Pattern match to return only numbers from the CTE
--and use STRING_AGG to rebuild it into a single string.
SELECT @String = STRING_AGG(Character,'')
FROM Characters
WHERE Character LIKE '[0-9]'
--allows going past the default maximum of 100 loops in the CTE
OPTION (MAXRECURSION 8000)
RETURN @String
END
GO
테스트
목적별 테스트:
SELECT dbo.GetNumbers(InputString) AS Numbers
FROM ( VALUES
('003Preliminary Examination Plan') --output: 003
,('Coordination005') --output: 005
,('Balance1000sheet') --output: 1000
,('(111) 222-3333') --output: 1112223333
,('1.38hello@f00.b4r#\-6') --output: 1380046
) testData(InputString)
퍼포먼스 테스트:테스트 데이터 설정 시작...
--Add table to hold test data
CREATE TABLE dbo.NumTest (String VARCHAR(8000))
--Make an 8000 character string with mix of numbers and letters
DECLARE @Num VARCHAR(8000) = REPLICATE('12tf56se',800)
--Add this to the test table 500 times
DECLARE @n INT = 0
WHILE @n < 500
BEGIN
INSERT INTO dbo.NumTest VALUES (@Num)
SET @n = @n +1
END
이제 dbo를 테스트합니다.GetNumbers 함수:
SELECT dbo.GetNumbers(NumTest.String) AS Numbers
FROM dbo.NumTest -- Time to complete: 1 min 7s
그런 다음 UDF를 상위에서 테스트하여 동일한 데이터에 대해 응답합니다.
SELECT dbo.udf_GetNumeric(NumTest.String)
FROM dbo.NumTest -- Time to complete: 3 mins 12s
소수점
소수점 처리에 필요한 경우 다음 방법 중 하나를 사용할 수 있습니다. 두 방법 간의 성능 차이는 눈에 띄지 않습니다.
'[0-9]'로로 합니다.'[0-9.]'Character LIKE '[0-9]'로로 합니다.ISNUMERIC(Character) = 1을 'SQL)로 취급합니다).
보너스
하면 쉽게 사항에 수 .WHERE Character LIKE '[0-9]'하다
WHERE Letter LIKE '[a-zA-Z]' --Get only lettersWHERE Letter LIKE '[0-9a-zA-Z]' --Remove non-alphanumericWHERE Letter LIKE '[^0-9a-zA-Z]' --Get only non-alphanumeric
이전 쿼리에서는 다음과 같은 결과를 얻을 수 있습니다.
'AAAA1234BBB333' >>> 출력: 1234
'-s++0!\a121234' >>> 출력: 0
아래 코드는 모든 숫자 문자를 반환합니다.
첫 번째 출력: 1234333
두 번째 출력: 01234
declare @StringAlphaNum varchar(255)
declare @Character varchar
declare @SizeStringAlfaNumerica int
declare @CountCharacter int
set @StringAlphaNum = 'AAAA1234BBBB3333'
set @SizeStringAlfaNumerica = len(@StringAlphaNum)
set @CountCharacter = 1
while isnumeric(@StringAlphaNum) = 0
begin
while @CountCharacter < @SizeStringAlfaNumerica
begin
if substring(@StringAlphaNum,@CountCharacter,1) not like '[0-9]%'
begin
set @Character = substring(@StringAlphaNum,@CountCharacter,1)
set @StringAlphaNum = replace(@StringAlphaNum, @Character, '')
end
set @CountCharacter = @CountCharacter + 1
end
set @CountCharacter = 0
end
select @StringAlphaNum
declare @puvodni nvarchar(20)
set @puvodni = N'abc1d8e8ttr987avc'
WHILE PATINDEX('%[^0-9]%', @puvodni) > 0 SET @puvodni = REPLACE(@puvodni, SUBSTRING(@puvodni, PATINDEX('%[^0-9]%', @puvodni), 1), '' )
SELECT @puvodni
다음을 사용하는 SQL Server 2017 이상용 솔루션:
DECLARE @T table (string varchar(50) NOT NULL);
INSERT @T
(string)
VALUES
('003Preliminary Examination Plan'),
('Coordination005'),
('Balance1000sheet');
SELECT
result =
REPLACE(
TRANSLATE(
T.string COLLATE Latin1_General_CI_AI,
'abcdefghijklmnopqrstuvwxyz',
SPACE(26)),
SPACE(1),
SPACE(0))
FROM @T AS T;
출력:
| 결과 |
|---|
| 003 |
| 005 |
| 1000 |
코드는 다음과 같이 동작합니다.
- 문자 a~z(대문자와 악센트 무시)를 공백으로 바꿉니다.
- 공백을 빈 문자열로 바꿉니다.
TRANSLATE추가 문자를 포함하도록 확장할 수 있습니다.
함수를 만들 권한은 없지만 다음과 같은 텍스트가 있습니다.
["blahblah012345679"]
그리고 중간에서 숫자를 뽑아낼 필요가 있었다.
이 경우 숫자는 문자열의 시작과 끝이 아니라 함께 그룹화되어 있다고 가정합니다.
select substring(column_name,patindex('%[0-9]%', column_name),patindex('%[0-9][^0-9]%', column_name)-patindex('%[0-9]%', column_name)+1)
from table name
@Epsicron의 답변에 약간의 수정을 가했을 뿐입니다.
SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%',
string) + 1) AS Number
FROM (values ('003Preliminary Examination Plan'),
('Coordination005'),
('Balance1000sheet')) as a(string)
임시 변수가 필요 없음
비록 이것이 구글 검색의 첫 번째 오래된 스레드이긴 하지만, 나는 이전에 나온 것과 다른 답을 생각해냈다.그러면 해당 기준이 무엇이든 문자열 내에서 유지할 항목에 대한 기준을 통과시킬 수 있습니다.몇 번이고 몇 번이고 호출할 수 있는 기능에 넣을 수 있습니다.
declare @String VARCHAR(MAX) = '-123. a 456-78(90)'
declare @MatchExpression VARCHAR(255) = '%[0-9]%'
declare @return varchar(max)
WHILE PatIndex(@MatchExpression, @String) > 0
begin
set @return = CONCAT(@return, SUBSTRING(@string,patindex(@matchexpression, @string),1))
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
end
select (@return)
이 UDF는 모든 유형의 문자열에서 작동합니다.
CREATE FUNCTION udf_getNumbersFromString (@string varchar(max))
RETURNS varchar(max)
AS
BEGIN
WHILE @String like '%[^0-9]%'
SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')
RETURN @String
END
먼저 숫자의 시작 길이를 확인한 후 문자열을 반대로 하여 첫 번째 위치를 다시 확인합니다(이렇게 하면 끝부터 숫자의 끝 위치가 나타납니다.두 숫자에서 모두 1을 뺀 후 문자열 전체 길이에서 빼면 숫자 길이만 나옵니다.SUBSTRING을 사용하여 번호를 얻습니다.
declare @fieldName nvarchar(100)='AAAA1221.121BBBB'
declare @lenSt int=(select PATINDEX('%[0-9]%', @fieldName)-1)
declare @lenEnd int=(select PATINDEX('%[0-9]%', REVERSE(@fieldName))-1)
select SUBSTRING(@fieldName, PATINDEX('%[0-9]%', @fieldName), (LEN(@fieldName) - @lenSt -@lenEnd))
T-SQL 함수는 텍스트에서 모든 정수를 읽고 왼쪽 또는 오른쪽에서 시작하여 지정된 인덱스에 있는 정수를 반환하며 시작 검색어도 사용합니다(옵션).
create or alter function dbo.udf_number_from_text(
@text nvarchar(max),
@search_term nvarchar(1000) = N'',
@number_position tinyint = 1,
@rtl bit = 0
) returns int
as
begin
declare @result int = 0;
declare @search_term_index int = 0;
if @text is null or len(@text) = 0 goto exit_label;
set @text = trim(@text);
if len(@text) = len(@search_term) goto exit_label;
if len(@search_term) > 0
begin
set @search_term_index = charindex(@search_term, @text);
if @search_term_index = 0 goto exit_label;
end;
if @search_term_index > 0
if @rtl = 0
set @text = trim(right(@text, len(@text) - @search_term_index - len(@search_term) + 1));
else
set @text = trim(left(@text, @search_term_index - 1));
if len(@text) = 0 goto exit_label;
declare @patt_number nvarchar(10) = '%[0-9]%';
declare @patt_not_number nvarchar(10) = '%[^0-9]%';
declare @number_start int = 1;
declare @number_end int;
declare @found_numbers table (id int identity(1,1), val int);
while @number_start > 0
begin
set @number_start = patindex(@patt_number, @text);
if @number_start > 0
begin
if @number_start = len(@text)
begin
insert into @found_numbers(val)
select cast(substring(@text, @number_start, 1) as int);
break;
end;
else
begin
set @text = right(@text, len(@text) - @number_start + 1);
set @number_end = patindex(@patt_not_number, @text);
if @number_end = 0
begin
insert into @found_numbers(val)
select cast(@text as int);
break;
end;
else
begin
insert into @found_numbers(val)
select cast(left(@text, @number_end - 1) as int);
if @number_end = len(@text)
break;
else
begin
set @text = trim(right(@text, len(@text) - @number_end));
if len(@text) = 0 break;
end;
end;
end;
end;
end;
if @rtl = 0
select @result = coalesce(a.val, 0)
from (select row_number() over (order by m.id asc) as c_row, m.val
from @found_numbers as m) as a
where a.c_row = @number_position;
else
select @result = coalesce(a.val, 0)
from (select row_number() over (order by m.id desc) as c_row, m.val
from @found_numbers as m) as a
where a.c_row = @number_position;
exit_label:
return @result;
end;
예:
select dbo.udf_number_from text(N'Text text 10 text, 25 term', N'term',2,1);
10을 반환한다.
이것은 가장 간단하고 쉬운 것 중 하나입니다.이것은 여러 발생에 대해서도 String 전체에서도 동작합니다.
CREATE FUNCTION dbo.fn_GetNumbers(@strInput NVARCHAR(500))
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @strOut NVARCHAR(500) = '', @intCounter INT = 1
WHILE @intCounter <= LEN(@strInput)
BEGIN
SELECT @strOut = @strOut + CASE WHEN SUBSTRING(@strInput, @intCounter, 1) LIKE '[0-9]' THEN SUBSTRING(@strInput, @intCounter, 1) ELSE '' END
SET @intCounter = @intCounter + 1
END
RETURN @strOut
END 단일 Common Table Expression(CTE; 공통 테이블 표현)을 사용한 솔루션에 따릅니다.
DECLARE @s AS TABLE (id int PRIMARY KEY, value nvarchar(max));
INSERT INTO @s
VALUES
(1, N'003Preliminary Examination Plan'),
(2, N'Coordination005'),
(3, N'Balance1000sheet');
SELECT * FROM @s ORDER BY id;
WITH t AS (
SELECT
id,
1 AS i,
SUBSTRING(value, 1, 1) AS c
FROM
@s
WHERE
LEN(value) > 0
UNION ALL
SELECT
t.id,
t.i + 1 AS i,
SUBSTRING(s.value, t.i + 1, 1) AS c
FROM
t
JOIN @s AS s ON t.id = s.id
WHERE
t.i < LEN(s.value)
)
SELECT
id,
STRING_AGG(c, N'') WITHIN GROUP (ORDER BY i ASC) AS value
FROM
t
WHERE
c LIKE '[0-9]'
GROUP BY
id
ORDER BY
id;
DECLARE @index NVARCHAR(20);
SET @index = 'abd565klaf12';
WHILE PATINDEX('%[0-9]%', @index) != 0
BEGIN
SET @index = REPLACE(@index, SUBSTRING(@index, PATINDEX('%[0-9]%', @index), 1), '');
END
SELECT @index;
「 」를 대체할 수 .[0-9][a-z] .CAST★★★★★★ 。
User Define Function을 사용하면 쿼리 속도가 크게 느려집니다.이 코드는 문자열에서 숫자를 추출합니다.
SELECT
Reverse(substring(Reverse(rtrim(ltrim( substring([FieldName] , patindex('%[0-9]%', [FieldName] ) , len([FieldName]) )))) , patindex('%[0-9]%', Reverse(rtrim(ltrim( substring([FieldName] , patindex('%[0-9]%', [FieldName] ) , len([FieldName]) )))) ), len(Reverse(rtrim(ltrim( substring([FieldName] , patindex('%[0-9]%', [FieldName] ) , len([FieldName]) ))))) )) NumberValue
FROM dbo.TableName
CREATE OR REPLACE FUNCTION count_letters_and_numbers(input_string TEXT)
RETURNS TABLE (letters INT, numbers INT) AS $$
BEGIN
RETURN QUERY SELECT
sum(CASE WHEN input_string ~ '[A-Za-z]' THEN 1 ELSE 0 END) as letters,
sum(CASE WHEN input_string ~ '[0-9]' THEN 1 ELSE 0 END) as numbers
FROM unnest(string_to_array(input_string, '')) as input_string;
END;
$$ LANGUAGE plpgsql;
재미삼아...
이 솔루션은 이전의 모든 솔루션과 다릅니다.viz:
- 함수를 생성할 필요가 없습니다.
- 패턴 매칭을 사용할 필요가 없습니다.
- 임시 테이블은 필요 없습니다.
- 이 솔루션에서는 재귀 공통 테이블식(CTE)을 사용합니다.
그러나 먼저 - 질문에서 이러한 문자열이 저장되는 위치가 지정되지 않았습니다.다음 솔루션에서는 CTE를 작성하여 이러한 문자열을 일종의 "소스 테이블"에 빠르고 더러운 방법으로 넣습니다.
주의: 이 솔루션은 재귀적 공통 테이블 표현식(CTE)을 사용하므로 여기서 2개의 CTE를 사용한다고 혼동하지 마십시오.첫 번째는 단순히 솔루션을 통해 데이터를 이용할 수 있도록 하는 것이지만, 이 문제를 해결하기 위해 필요한 것은 두 번째 CTE뿐입니다.코드를 조정하여 이 두 번째 CTE 쿼리를 기존 테이블, 뷰 등으로 만들 수 있습니다.
마지막으로, 제 코딩은 상세하고, 무슨 일이 일어나고 있는지를 설명하는 열과 CTE 이름을 사용하려고 합니다.이 솔루션을 조금 심플하게 할 수 있습니다.재미삼아 몇 가지 유사 전화번호를 (예상된 형식이나 비정상적인 형식일 수도 있음) 추가했습니다.
with SOURCE_TABLE as (
select '003Preliminary Examination Plan' as numberString
union all select 'Coordination005' as numberString
union all select 'Balance1000sheet' as numberString
union all select '1300 456 678' as numberString
union all select '(012) 995 8322 ' as numberString
union all select '073263 6122,' as numberString
),
FIRST_CHAR_PROCESSED as (
select
len(numberString) as currentStringLength,
isNull(cast(try_cast(replace(left(numberString, 1),' ','z') as tinyint) as nvarchar),'') as firstCharAsNumeric,
cast(isNull(cast(try_cast(nullIf(left(numberString, 1),'') as tinyint) as nvarchar),'') as nvarchar(4000)) as newString,
cast(substring(numberString,2,len(numberString)) as nvarchar) as remainingString
from SOURCE_TABLE
union all
select
len(remainingString) as currentStringLength,
cast(try_cast(replace(left(remainingString, 1),' ','z') as tinyint) as nvarchar) as firstCharAsNumeric,
cast(isNull(newString,'') as nvarchar(3999)) + isNull(cast(try_cast(nullIf(left(remainingString, 1),'') as tinyint) as nvarchar(1)),'') as newString,
substring(remainingString,2,len(remainingString)) as remainingString
from FIRST_CHAR_PROCESSED fcp2
where fcp2.currentStringLength > 1
)
select
newString
,* -- comment this out when required
from FIRST_CHAR_PROCESSED
where currentStringLength = 1
그래서 무슨일이야?
기본적으로 CTE에서는 첫 번째 캐릭터를 선택하고try_cast(의사 참조)에 캐스트하다tinyint(이것은, 1 자리수의 숫자에 충분한 데이터 타입입니다).SQL Server의 유형 캐스팅 규칙에는 빈 문자열(또는 공백)이 0으로 해결되므로nullif공백과 빈 문자열이 늘로 해결되도록 강제하기 위해 추가됩니다(이것에 대해서는, 소스 데이터에 공백이 있는 경우는, 항상 제로 문자가 표시됩니다).
또한 CTE는 첫 번째 문자 뒤에 모든 것을 반환합니다.이것은 CTE의 재귀 호출에 대한 입력이 됩니다.즉, 다음 문자를 처리해 봅시다.
마지막으로 필드newString(두 번째) CTE가 생성됩니다.SELECT)을(를)재귀적 CTE의 경우 데이터 유형은 두 유형 간에 일치해야 합니다.SELECT지정된 열에 대한 문 - 열 크기를 포함합니다.1개의 캐릭터를 추가하고 있기 때문에, 그 캐릭터를 nvarchar(1)에 캐스팅하고,newString(지금까지) nvarchar(3999)로 되어 있습니다.연결하면 nvarchar(4000)가 되며, 이는 처음 수행한 유형 주조와 일치합니다.SELECT.
이 쿼리를 실행하고,WHERE절에서는 무슨 일이 일어나고 있는지 알 수 있지만 행의 순서가 이상할 수 있습니다.(단일 입력값과 관련된 모든 행이 함께 그룹화되어 있는 것은 아닙니다만, 계속 따라갈 수 있습니다.)
엄격한 표현에 기초한 솔루션을 원하는 소수의 사람들에게 도움이 될 수 있는 흥미로운 옵션이었으면 합니다.
Oracle의 경우
다음을 사용하여 원하는 것을 얻을 수 있습니다.
SUBSTR('ABCD1234EFGH',REGEXP_INSTR ('ABCD1234EFGH', '[[:digit:]]'),REGEXP_COUNT ('ABCD1234EFGH', '[[:digit:]]'))
쿼리 예시:
SELECT SUBSTR('003Preliminary Examination Plan ',REGEXP_INSTR ('003Preliminary Examination Plan ', '[[:digit:]]'),REGEXP_COUNT ('003Preliminary Examination Plan ', '[[:digit:]]')) SAMPLE1,
SUBSTR('Coordination005',REGEXP_INSTR ('Coordination005', '[[:digit:]]'),REGEXP_COUNT ('Coordination005', '[[:digit:]]')) SAMPLE2,
SUBSTR('Balance1000sheet',REGEXP_INSTR ('Balance1000sheet', '[[:digit:]]'),REGEXP_COUNT ('Balance1000sheet', '[[:digit:]]')) SAMPLE3 FROM DUAL
Postgres를 사용하고 있고 '2000 - some sample text'와 같은 데이터가 있는 경우 하위 문자열과 위치 조합을 시도합니다. 그렇지 않으면 시나리오에서 구분 기호가 없으면 regex를 입력해야 합니다.
SUBSTRING(Column_name from 0 for POSITION('-' in column_name) - 1) as
number_column_name
언급URL : https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string
반응형
'source' 카테고리의 다른 글
| std::string 인스턴스를 소문자로 변환하는 방법 (0) | 2023.04.22 |
|---|---|
| VBA에서 File System Object를 사용하려면 어떻게 해야 합니까? (0) | 2023.04.22 |
| Python - 각 목록의 네 번째 요소별로 목록을 정렬하려면 어떻게 해야 합니까? (0) | 2023.04.22 |
| X시간보다 오래된 파일을 삭제하는 방법 (0) | 2023.04.22 |
| T-SQL에서의 PRINT 문 (0) | 2023.04.22 |