Delete specific characters from the column data of the selected query

Question

I searched and searched for a solution to my problem, and although I found some that might help, I'm not sure if this is the right way to do this for what I need. Keep in mind that I'm learning and quite new to SQL. But I have two tables that I want to query using an internal join.

Both tables have a column named CAVITY , but they contain values ​​in different formats. One table holds the value as H02, and the other table has the value as 2.

I want to enable the inner join in this column using the H02 format, but I do not want to UPDATE the table containing the value as a single number (for data entry purposes). For example, if a column with one table 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 too. So basically I want to remove H and 0 (but only if there is 0 that immediately follows H). If there is a way to do the opposite and add H / H0 from the results of another table.

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

It doesn't matter to me whether my query results are version H0 or not; I just need teams to work; I can adapt to the result at the end.

I am using SQL Server 2005

I have the rest of the request and it works fine; I just need to add an extra add. Please help! Any help MAY BE RECOGNIZED. thank you

Answer 1

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

The first removes H0:

REPLACE(cavity,'H0','')

And then the second replaces the remaining H:

REPLACE(output_of_previous,'H','')

Answer 2

One way to do this is as follows:

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

(It is assumed that 2 means table2.cavity is stored as an integer.

Great care. You should pay attention to the request plan. When writing such queries, it’s quite simple to end the plan using nested loop joining. If your tables are small, it doesn't really matter. However, even moderate-sized tables can create a very inefficient query plan.

Answer 3

 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 

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