Issue with patindex and unicode character '-'

Question

I have a string called Dats which is either of the general appearence xxxx-nnnnn (where x is a character, and n is a number) or nnn-nnnnnn.

I want to return only the numbers.

For this I've tried:

SELECT Distinct dats, 
Left(SubString(artikelnr, PatIndex('%[0-9.-]%', artikelnr), 8000), PatIndex('%[^0-9.-]%', SubString(artikelnr, PatIndex('%[0-9.-]%', artikelnr), 8000) + 'X')-1)
FROM ThatDatabase

It is almost what I want. It removes the regular characters x, but it does not remove the unicode character -. How can I remove this as well? And also, it seems rather ineffective to have two PatIndex functions for every row, is there a way to avoid this? (This will be used on a big database where the result of this Query will be used as keys).

EDIT: Updated as a new database sometimes contained additional -'s or . together with -.

DECLARE @T as table
(
    dats nvarchar(10)
)

INSERT INTO @T VALUES
('111BWA30'),
('115-200-11')
('115-22.4-1')
('10.000.22')
('600F-FFF200')

The query you posted is syntactically incorrect. – Giorgos Betsos Feb 14 '17 at 09:26 — Giorgos Betsos, Feb 14 '17 at 09:26

score 4 · Accepted Answer · answered Feb 14 '17 at 09:25

I wasn't sure if you wanted the numbers before the - char as well, but if you do, here is one way to do it:

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T as table
(
    dats nvarchar(10)
)

INSERT INTO @T VALUES
('abcde-1234'),
('23-343')

The query:

SELECT  dats,
        case when patindex('%[^0-9]-[0-9]%', dats) > 0 then
            right(dats, len(dats) - patindex('%-[0-9]%', dats))
        else
            stuff(dats, charindex('-', dats), 1, '')
        end As NumbersOnly
FROM @T

Results:

dats        NumbersOnly
abcde-1234  1234
23-343      23343

If you want the only the numbers to the right of the - char, it's simpler:

SELECT  dats,
        right(dats, len(dats) - patindex('%-[0-9]%', dats)) As RightNumbersOnly
FROM @T

Results:

dats        RightNumbersOnly
abcde-1234  1234
23-343      343

I want to remove any characters and `-` values from the string so only the integers are left. I see that this function does that. Id like to ask however, seeing as this worked so good I tested it on Another database which would have the same appaerence, but I see now that it sometimes contains more than one `-`, or sometimes `.` as well as `-`. Is there any neat way to adjust for this? Will update the OP soon with a sample table. — Cenderze, Feb 14 '17 at 09:40
You are correct. It too big of an alteration to have as an edit. Accepting your answer, and might consider using my "edit" as a later question after I've thought some time more of it. Thanks again for a wonderful, clear, and thought-through answer. — Cenderze, Feb 14 '17 at 11:57

score 1 · Answer 2 · answered Feb 14 '17 at 10:00

If you know which characters you need to remove then use REPLACE function

DECLARE @T as table
(
    dats nvarchar(100)
)

INSERT INTO @T 
VALUES
('111BWA30'),
('115-200-11'),
('115-22.4-1'),
('10.000.22'),
('600F-FFF200')

SELECT REPLACE(REPLACE(dats, '.', ''), '-', '')
FROM @T

Issue with patindex and unicode character '-'

2 Answers2