CODEWITHSUNDEEP
sqlsql-server
user3782230
user3782230

Reputation: 155

SQL Server Select only part of String

If someone can help me with selecting only left part of string before '('.

For example I have this string:

SS0054(BOSCH)

and I want to select only text to the LEFT of the (, and ignore the right part.

I have managed to select left part, but in my data I also have string values without ( in it for example just SS0054, but then I get error:

Invalid length parameter passed to the left function

How can I select all values, to the left of ( in strings where a ( exists?

Here is my code:

SELECT UPPER(LEFT(RTRIM(LTRIM('SS0054(BOSCH)')), CHARINDEX('(', REVERSE(RTRIM(LTRIM('SS0054(BOSCH)')))) - 1));

Thanks in advance

Upvotes: 1

Views: 1314

Answers (3)

sa-es-ir
sa-es-ir

Reputation: 5042

Just check '(' in your string like this:

DECLARE @string NVARCHAR(50)='78BD0920-2(VALEO)'

SELECT CASE WHEN CHARINDEX('(',@string)!=0
THEN 
UPPER(LEFT(RTRIM(LTRIM(@string)), CHARINDEX('(', @string) - 1))
 ELSE @string -- ( not exist
  end

Upvotes: 3

splrs
splrs

Reputation: 2432

Use a CASE statement, and you can simplify your original code to find the text before the '('.

SELECT
  CASE
    WHEN CHARINDEX('(', MY_STR) <> 0 THEN
        UPPER(SUBSTRING(MY_STR, 0, CHARINDEX('(', MY_STR)))
    ELSE
        UPPER(MY_STR) -- or whatever else you wish to display
    END

Upvotes: 2

N_Mujtaba
N_Mujtaba

Reputation: 9

It seems to me that you always want the first 6 characters. Will substring work?

SELECT SUBSTRING('SS0054(BOSCH)', 0,7) AS partString;

You can add the LTRIM, RTRIM if you require

SELECT SUBSTRING(RTRIM(LTRIM(' SS0054(BOSCH) ' )), 0,7) AS partString;

Upvotes: -1

Related Questions