• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

Trimming SQL Select command

Hi guys,

I have a SELECT staement and one of the fields is...

table.Manufacturer

A typical manufacturer record would be.... Microsoft(MSO)

How can I return just the characters between the brackets? Bearing in mind the actual manufacturer name may be different lengths, ie SONY(SON)

Cheers,
Dean.
0
deanlee17
Asked:
deanlee17
1 Solution
 
lwadwellCommented:
have look at
with test_data as (
select 'SONY(SON)' as Manufacturer union all
select 'Microsoft(MSO)'
)
select Manufacturer, substring(Manufacturer, charindex('(', Manufacturer)+1, charindex(')', Manufacturer)-charindex('(', Manufacturer)-1 )
from test_data

Open in new window

0
 
SamuelShawCommented:
As long as you'll only have the one set of parentheses, you can try this to get the substring.
SELECT SUBSTRING(table.Manufacturer, PATINDEX(table.Manufacturer, '%(%') - 1, (PATINDEX(table.Manufacturer, '%)%') - PATINDEX(table.Manufacturer, '%(%')) - 2)

Open in new window

0
 
UnifiedISCommented:
DECLARE @S varchar(100)
SET @S = 'Microsoft(MSO)'
DECLARE @LeftBracket char(1)
DECLARE @RightBracket char(1)
SET @LeftBracket = '('
SET @RightBracket = ')'
SELECT REPLACE(SUBSTRING(@S, CHARINDEX(@LeftBracket, @S) + 1,LEN(@S)), @RightBracket,'')

Assumes the (MSO) is always at the right edge
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
deanlee17Author Commented:
Guys I am using a basic report writer so no T-SQL
0
 
UnifiedISCommented:
Ok, what pieces can't you use?
Mine relies upon CHARINDEX, REPLACE, SUBSTRING and LEN
You don't need the bracket variables, I used them for clarity
0
 
deanlee17Author Commented:
Hi UnifiedIS,

So I would use ur example as follows...

DECLARE @S varchar(100)
SET @S = table.Manufacturer
DECLARE @LeftBracket char(1)
DECLARE @RightBracket char(1)
SET @LeftBracket = '('
SET @RightBracket = ')'
SELECT REPLACE(SUBSTRING(@S, CHARINDEX(@LeftBracket, @S) + 1,LEN(@S)), @RightBracket,'') as Manufacturer?, table.Field1, table.Field2, table.Field3 from table
0
 
UnifiedISCommented:
Not quite, ignore the @S variable, that was so I could generate an example

DECLARE @LeftBracket char(1)
DECLARE @RightBracket char(1)
SET @LeftBracket = '('
SET @RightBracket = ')'
SELECT REPLACE(SUBSTRING(table.Manufacturer, CHARINDEX(@LeftBracket, table.Manufacturer) + 1,LEN(table.Manufacturer)), @RightBracket,'') as Manufacturer, table.Field1, table.Field2, table.Field3 from table
0
 
deanlee17Author Commented:
UnifiedIS you diamond. That works, cheers pal.
0
 
deanlee17Author Commented:
Great help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now