CODEWITHSUNDEEP
sqlsql-server-2005jointrim
user1768816
user1768816

Reputation: 61

Remove specific characters from column data of select query

I have searched and searched for a solution to my problem and even though I've found some that may help, I'm not sure it's the right way to do it for what I need. Keep in mind I am learning and fairly new to SQL. But I have two tables I want to query using an inner join.

There is a column named CAVITY in both tables but they hold the values in different formats. One table holds the value as H02 and the other table hold the value as just 2.

I want to inner join on this column using the H02 format but don't want to UPDATE the table that holds the value as a single number(for data entry purposes). So for example, if the one table column has H02 and the other has 2, I want it to be joined. If one table has H13 and the other 13, I want it to be a join as well. So basically, I am wanting to remove the H and 0 (but only if there is a 0 that directly follows the H). Unless there is a way to do the opposite and add the H / H0 from the results of the other table.

example data
table1.cavity   table2.cavity
     H01      =    1
     H02      =    2
     H10      =    10
     H12      =    12

It doesn't matter to me if my query results are the H0 version or not; I just need the joins to work; I can accommodate for either result in the end.

I am using SQL Server 2005

I have the rest of the query and it works ok; I just need this extra join added. Please help! Any help is GREATLY APPRECIATED. Thank you

Upvotes: 5

Views: 27017

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

CREATE FUNCTION [dbo].[RemoveAlphaCharacters](@Temp VarChar( 1000))
RETURNS int
AS
BEGIN
  WHILE PatIndex ('%[^0-9]%', @Temp) > 0
  SET @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
  RETURN @Temp
END

It's possible use in join clause like this

SELECT a.col1 a, b.col1 b
FROM tablea a JOIN tableb b ON dbo.RemoveAlphaCharacters(a.col1) = b.col1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

One way you can do this is as follows:

 from table1 join
      table2
      on cast(substring(table1.cavity, 2, 100) as int) = table2.cavity

(This is assuming the 2 means that table2.cavity is stored as an integer.

A big caution, though. You need to pay attention to the query plan. When writing such queries, it is quite easy to end up with a plan using a nested loop join. If your tables are small, this isn't a big deal. However, even moderately sized tables could produce a highly inefficient query plan.

Upvotes: 0

Mitch Satchwell
Mitch Satchwell

Reputation: 4830

REPLACE(REPLACE(cavity,'H0',''),'H','')

The first removes H0:

REPLACE(cavity,'H0','')

And then the second replaces any remaining H's:

REPLACE(output_of_previous,'H','')

Upvotes: 4

Related Questions