CODEWITHSUNDEEP
sql-server
Bernard Polman
Bernard Polman

Reputation: 855

SQL Server - remove left part of string before a specific character

I have a VARCHAR value that looks like this:

5.95 $ Additional fees

How can I remove everything left from character '$' (including that character) ? So that I get the following result:

Additional fees

The '$' is always present.

Upvotes: 0

Views: 597

Answers (3)

Karan Ekkawala
Karan Ekkawala

Reputation: 327

Hello do it like below syntax enter image description here

declare @temp nvarchar(max)='5.95 $ Additional fees'
select SUBSTRING(@temp,charindex('$',@temp)+1,len(@temp)-1)

Upvotes: 3

iVad
iVad

Reputation: 573

You can use SUBSTRING get the particular string and CHARINDEX function to get index of special character, in your case $.

DECLARE @Var VARCHAR(100)

SET @Var = '5.95 $ Additional fees'

SELECT SUBSTRING(@Var, CHARINDEX('$', @Var) + 1, LEN(@Var) - LEN(LEFT(@Var, CHARINDEX('$', @Var))))

Upvotes: 2

Thom A
Thom A

Reputation: 95554

STUFF and CHARINDEX would be the simpliest way, in my opinion:

SELECT STUFF(YourColumn,1, CHARINDEX('$',YourColumn),'')
FROM (VALUES('5.95 $ Additional fees'))V(YourColumn);

Note that as $ has a whitespace afterwards, the value returned will have a leading whitespace (' Additional fees'). You could use TRIM (or LTRIM and RTRIM on older versions of SQL Server) to remove this, if it isn't wanted.

I haven't assumed that the portion string to be replaced is CHARINDEX('$',YourColumn)+1, as we have one sample. As far as we know, you could also have values such as '10.99$Base Cost'. If the +1 was used, it would return 'ase Cost' for such a value.

Upvotes: 4

Related Questions