CODEWITHSUNDEEP
sql-server-2008replacetranslate
MISNole
MISNole

Reputation: 1062

SQL Server equivalent of Translate (DB2)

I'm looking for a way to remove any record that begins with 3 letters. In my screenshot, I've highlighted the rows I'd like to have filtered out. It can be any combination of letters though, not just these examples.

enter image description here

I don't want to lose all records that begin with a letter, only if they begin with 3 letters. Records 2 & 13 (beginning with a single letter) are records that should remain.

DB2 (and I think Oracle) has function Translate that handles this, but is there a SQL Server alternative?

DB2:

TRANSLATE( 
LEFT( LTRIM( hos.hpid ), 3 ),
'AAAAAAAAAAAAAAAAAAAAAAAAA', 
'BCDEFGHIJKLMNOPQRSTUVWXYZ'
 ) <> 'AAA'

Upvotes: 0

Views: 642

Answers (1)

Code Different
Code Different

Reputation: 93181

You can use the LIKE operator:

DELETE SomeTable
    WHERE hospid LIKE '[a-z][a-z][a-z]%'

If your collation is case-sensitive, you will need to include the capital letters too: [a-zA-Z].

Upvotes: 1

Related Questions