mehtat_90
mehtat_90

Reputation: 628

Like operator SQL

I have a data set (approx 900k lines) where I need to split a data based on a '(' or ')'. For Example Table A data:-

> Vendor      Is_Active
 ABC(1263)        1
 efgh (187        1
 pqrs 890ag)      1
 xyz              1
 lmno(488)        1
 (9867-12)        1

Output

 ID        Name   
 1263      ABC    
 187      efgh   
 890ag    pqrs
          xyz
 488      lmno
 9867-12  

I tried query

      SELECT
                vendor,
                CASE WHEN vendor LIKE '%(%' OR      vendor LIKE '%)%' 
    THEN REPLACE(REPLACE(RIGHT(Vendor, charindex(' ', reverse(vendor)) - 1),'(',''),')','') 
END AS 'test'
        FROM
                tableA 

Error :- Msg 536, Level 16, State 4, Line 13 Invalid length parameter passed to the RIGHT function.

Upvotes: 0

Views: 70

Answers (3)

uzi
uzi

Reputation: 4146

You can remove chars ( and ) then search for number occurrence. Check this query

declare @t table (
    vendor varchar(100)
)

insert into @t values
('ABC(1263)')
,('efgh (187')
,('pqrs 890ag)')
,('xyz')
,('lmno(488)')
,('(9867-12)')

select 
    ID = case when p = 0 then '' else substring(v, p, len(v)) end
    , Name = case when p = 0 then v else left(v, p - 1) end
from 
    @t
    cross apply (select v = replace(replace(vendor, '(', ''), ')', '')) q1
    cross apply (select p = patindex('%[0-9]%', v)) q2

Output

ID         Name
---------------
1263       ABC
187        efgh 
890ag      pqrs 
           xyz
488        lmno
9867-12 

Upvotes: 1

ppathak31
ppathak31

Reputation: 11

SELECT 
(CASE WHEN Vendor LIKE '%(%)' THEN SUBSTRING(Vendor,CHARINDEX('(',Vendor)+1,CHARINDEX(')',Vendor)-CHARINDEX('(',Vendor)-1)
        WHEN Vendor LIKE '%(%' THEN SUBSTRING(Vendor,CHARINDEX('(',Vendor)+1,LEN(Vendor))
        WHEN Vendor LIKE '%)%' THEN SUBSTRING(Vendor,CHARINDEX(' ',Vendor)+1,(CHARINDEX(')',Vendor)-CHARINDEX(' ',Vendor))-1)

ELSE ''
END )AS ID ,

(CASE WHEN Vendor LIKE '%(%)' THEN SUBSTRING(Vendor,1,CHARINDEX('(',Vendor)-1) 
  WHEN Vendor LIKE '%(%' THEN SUBSTRING(Vendor,1,CHARINDEX('(',Vendor)-1)
  WHEN Vendor LIKE '%)%' THEN SUBSTRING(Vendor,1,CHARINDEX(' ',Vendor))

ELSE Vendor END ) AS Name
FROM Table A

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270351

Hmmm. I'm thinking:

select v.*, v2.name,
       replace(stuff(v.x, 1, len(v2.name) + 1, ''), ')', '') as id
from (values ('ABC(1263)'), ('abc'), ('(1234)')) v(x) cross apply
     (values (left(v.x, charindex('(', v.x + '(') - 1))) v2(name);

I find apply useful for repetitive string operations.

Upvotes: 1

Related Questions