Ik heb gegevens zoals deze:
string 1: 003Preliminary Examination Plan
string 2: Coordination005
string 3: Balance1000sheet
De output die ik verwacht is
string 1: 003
string 2: 005
string 3: 1000
En ik wil het in SQL implementeren.
Antwoord 1, autoriteit 100%
Maak eerst deze UDF
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
Gebruik nu de functionals
SELECT dbo.udf_GetNumeric(column_name)
from table_name
Ik hoop dat dit je probleem heeft opgelost.
Antwoord 2, autoriteit 40%
Probeer deze eens –
Vraag:
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
Uitvoer:
----------
003
005
1000
Antwoord 3, autoriteit 16%
Vraag:
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
Antwoord 4, autoriteit 10%
Probeer het:
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
Antwoord 5, autoriteit 6%
Alleen getallen uit een string halen kan in een oneliner.
Probeer dit:
SUBSTRING('your-string-here', PATINDEX('%[0-9]%', 'your-string-here'), LEN('your-string-here'))
NB: werkt alleen voor de eerste int in de tekenreeks, bijvoorbeeld: abc123vfg34 geeft 123 terug.
Antwoord 6, autoriteit 3%
Bij de vorige zoekopdrachten krijg ik deze resultaten:
‘AAAA1234BBBB3333’ >>>> Uitgang: 1234
‘-çã+0!\aº1234’ >>>> Uitgang: 0
De onderstaande code retourneert Alle numerieke tekens:
Eerste uitvoer: 12343333
2e uitgang: 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
Antwoord 7, Autoriteit 2%
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
Antwoord 8
Ik had geen rechten om functies te maken, maar had tekst zoals
["blahblah012345679"]
en nodig om de cijfers uit het midden te halen
OPMERKING Dit gaat ervan uit dat de cijfers worden gegroepeerd en niet aan het begin en het einde van de tekenreeks.
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
Antwoord 9
Deze UDF werkt voor alle soorten snaren:
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
Antwoord 10
Gewoon een beetje wijziging aan @epsicron’s antwoord
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)
Geen behoefte aan een tijdelijke variabele
Antwoord 11
Hoewel dit een oude draad is, is het de eerste in Google Search, ik bedacht met een ander antwoord dan wat eerder kwam. Hiermee kunt u uw criteria doorgeven voor wat u binnen een touw kunt blijven, wat deze criteria ook mogelijk is. U kunt het in een functie plaatsen om opnieuw en opnieuw te bellen als u wilt.
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)
Antwoord 12
Ik vond dat deze aanpak ongeveer 3x sneller werkt dan het topgerei. Maak de volgende functie, DBO.GetNummers:
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
Testen
Test met een doel:
SELECT dbo.GetNumbers(InputString) AS Numbers
FROM ( VALUES
('003Preliminary Examination Plan') --output: 003
,('Coordination005') --output: 005
,('Balance1000sheet') --output: 1000
,('(111) 222-3333') --output: 1112223333
,('[email protected]#\-6') --output: 1380046
) testData(InputString)
Testen op prestaties:
Begin met het instellen van de testgegevens…
--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
Nu de dbo.GetNumbers-functie aan het testen:
SELECT dbo.GetNumbers(NumTest.String) AS Numbers
FROM dbo.NumTest -- Time to complete: 1 min 7s
Vervolgens het testen van de UDF van het meest gestemde antwoordop dezelfde gegevens.
SELECT dbo.udf_GetNumeric(NumTest.String)
FROM dbo.NumTest -- Time to complete: 3 mins 12s
Inspiratie voor dbo.GetNumbers
Decimalen
Als je het nodig hebt om decimalen te verwerken, kun je een van de volgende benaderingen gebruiken. Ik heb geen merkbare prestatieverschillen tussen beide gevonden.
- verander
'[0-9]'in'[0-9.]' - verander
Character LIKE '[0-9]'inISNUMERIC(Character) = 1(SQL behandelt een enkel decimaalteken als numeriek)
Bonus
Je kunt dit eenvoudig aanpassen aan verschillende vereisten door WHERE Character LIKE '[0-9]'uit te wisselen met de volgende opties:
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
Antwoord 13
T-SQL-functie om alle gehele getallen uit tekst te lezen en die van de aangegeven index terug te geven, beginnend van links of rechts, ook met een startzoekterm (optioneel):
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;
Voorbeeld:
select dbo.udf_number_from text(N'Text text 10 text, 25 term', N'term',2,1);
geeft 10 terug;
Antwoord 14
Ontdek eerst de startlengte van het nummer en draai vervolgens de tekenreeks om om de eerste positie opnieuw te vinden (waardoor u de eindpositie van het nummer vanaf het einde krijgt). Als u nu 1 aftrekt van beide getallen en deze aftrekt van de hele lengte van de string, krijgt u alleen de lengte van het getal. Haal nu het nummer op met 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))
Antwoord 15
Dit is een van de eenvoudigste en gemakkelijkste. Dit werkt ook op de hele String voor meerdere gevallen.
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 Antwoord 16
In Orakel
Je kunt krijgen wat je wilt met dit:
SUBSTR('ABCD1234EFGH',REGEXP_INSTR ('ABCD1234EFGH', '[[:digit:]]'),REGEXP_COUNT ('ABCD1234EFGH', '[[:digit:]]'))
Voorbeeldquery:
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
Antwoord 17
In godsnaam…
Deze oplossing is anders dan alle eerdere oplossingen, namelijk:
- Het is niet nodig om een functie aan te maken
- Het is niet nodig om patroonherkenning te gebruiken
- Er is geen tijdelijke tafel nodig
- Deze oplossing gebruikt een recursieve algemene tabeluitdrukking (CTE)
Maar eerst – merk op dat de vraag niet specificeert waar dergelijke strings worden opgeslagen. In mijn onderstaande oplossing maak ik een CTE als een snelle en vuile manier om deze strings in een soort “brontabel” te plaatsen.
Let ook op: deze oplossing gebruikt een recursieve algemene tabel uitdrukking(CTE) – raak dus niet in de war door het gebruik van twee CTE’s hier. De eerste is gewoon om de gegevens beschikbaar te maken voor de oplossing – maar het is alleen de tweede CTE die nodig is om dit probleem op te lossen. U kunt de code aanpassen om deze tweede CTE-query uw bestaande tabel, weergave, enz. te laten maken.
Ten slotte – mijn codering is uitgebreid, ik probeer kolom- en CTE-namen te gebruiken die uitleggen wat er aan de hand is en je kunt deze oplossing misschien een beetje vereenvoudigen. Ik heb een paar pseudo-telefoonnummers toegevoegd met wat (verwachte en atypische, zoals het geval kan zijn) opmaak voor de lol.
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
Dus wat is hier aan de hand?
In onze CTE selecteren we het eerste teken en gebruiken we try_cast(zie docs) om het naar een tinyintte casten (die groot genoeg is gegevenstype voor een cijfer van één cijfer). Merk op dat de type-casting-regels in SQL Server zeggen dat een lege tekenreeks (of een spatie, wat dat betreft) zal worden omgezet in nul, dus de nullifwordt toegevoegd om spaties en lege tekenreeksen te forceren om op te lossen naar null (zie discussie) (anders zou ons resultaat een nulteken opnemen telkens wanneer een spatie wordt aangetroffen in de brongegevens).
De CTE retourneert ook alles na het eerste teken – en dat wordt de invoer voor onze recursieve aanroep op de CTE; met andere woorden: laten we nu het volgende karakter verwerken.
Ten slotte wordt het veld newStringin de CTE gegenereerd (in de tweede SELECT) via aaneenschakeling. Bij recursieve CTE’s moet het gegevenstype overeenkomen tussen de twee SELECT-instructies voor een bepaalde kolom – inclusief de kolomgrootte. Omdat we weten dat we (maximaal) één teken toevoegen, casten we dat teken naar nvarchar(1) en casten we de newString(tot nu toe) als nvarchar(3999). Aaneengeschakeld, zal het resultaat nvarchar(4000) zijn – wat overeenkomt met het type casting dat we uitvoeren in de eerste SELECT.
Als u deze query uitvoert en de WHERE-clausule uitsluit, krijgt u een idee van wat er aan de hand is, maar de rijen kunnen in een vreemde volgorde staan. (Je zult niet noodzakelijk alle rijen zien die betrekking hebben op een enkele invoerwaarde gegroepeerd – maar je zou nog steeds in staat moeten zijn om te volgen).
Ik hoop dat het een interessante optie is die een paar mensen kan helpen die een strikt op expressie gebaseerde oplossing willen.
Antwoord 18
Als je Postgres gebruikt en je hebt gegevens zoals ‘2000 – wat voorbeeldtekst’, probeer dan de combinatie van subtekenreeks en positie, anders als er in je scenario geen scheidingsteken is, moet je regex schrijven:
SUBSTRING(Column_name from 0 for POSITION('-' in column_name) - 1) as
number_column_name