-4

In my database I have a string that looks like this "P1_123456".

I am splitting the P1_ from the rest with the following:

right([PersNr], charindex('_', reverse([PersNr])) - 1) as PersNr

That leaves me with a single "space" after the last number. How can I prevent that space or remove it? I know how to remove spaces normally but not in combination with what I already have.

Improve this question
3
  • 5
    Check if the column PersNr itself has any leading and trailing whitespaces in the data. Moreover, you can use LTRIM(RTRIM(RIGHT([PersNr], CHARINDEX('_', REVERSE([PersNr])) - 1))) AS PersNr to trim those left and right unnecessary spaces. Commented Jun 3, 2024 at 6:27
  • replace(value, ' ', '')? Commented Jun 3, 2024 at 6:33
  • 1
    There is no space left in the example you are showing? dbfiddle.uk/zw27oDlE Please provide a minimal reproducible example. Commented Jun 3, 2024 at 6:34

2 Answers 2

Reset to default
1

If there are simple spaces then RTRIM(PersNr) like

Right( RTrim( PersNr ), Charindex( '_', Reverse( RTrim( PersNr )) -1 )

will work. However if there are trailing non breaking spaces ( Dec 160 instead of 32 ) then Replace is first required.

Alternative to optimise performance can be

Rtrim( Right( PersNr, Charindex( '_', Reverse( PersNr ) -1 ))

However if this extraction is necessary to generate a criteria (that goes into a WHERE clause) the better way is an Inline Table Value Function. If this is the case, please provide a new question with full reproducible data set. Or read up on ITVFs.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

you can use RTRIM() function to remove trailing spaces, combining it with your original query. It looks like this

RTRIM(right([PersNr], charindex('_', reverse([PersNr])) - 1)) as PersNr
Improve this answer

1 Comment

That's identical to the same existing, accepted answer, just without the helpful explanation. What does your answer add that isn't already provided?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.