sql - Merging substrings from two tables to a join - Stack Overflow

admin管理员组

文章数量:1401609

I have two different tables with a column called name. I will refer to the tables as Names1 and Names1a. One row in Names1, for example has data formatted as XXX10.R.999.15, Names1a has all of the characters and dots dropped, and is 199915 with a character at the end. The R shown in Names1 could be R or A, but to Names1A, if the value is R, Names1A has an A at the end of it, and is 1099915A. If Names1 has an C, then names1A would have a D at the end making it 1099915D. Ultimately, I'd need Names1 value to reflect the same as Names1A value to join the two tables, which are in separate databases.

I used substrings to extract the unnecessary characters from Names1 and tried case expression for the determination of the end value, but when I try to join the results an getting an error that the values aren't columns. Any ideas on how I can make this work?

Table1:Names1

Name.
XX10.R.999.15
XX10.C.999.15

I have two different tables with a column called name. I will refer to the tables as Names1 and Names1a. One row in Names1, for example has data formatted as XXX10.R.999.15, Names1a has all of the characters and dots dropped, and is 199915 with a character at the end. The R shown in Names1 could be R or A, but to Names1A, if the value is R, Names1A has an A at the end of it, and is 1099915A. If Names1 has an C, then names1A would have a D at the end making it 1099915D. Ultimately, I'd need Names1 value to reflect the same as Names1A value to join the two tables, which are in separate databases.

I used substrings to extract the unnecessary characters from Names1 and tried case expression for the determination of the end value, but when I try to join the results an getting an error that the values aren't columns. Any ideas on how I can make this work?

Table1:Names1

Name.
XX10.R.999.15
XX10.C.999.15

Table2:Names1A

Name.
1099915A
1099915D

Results would be a new column to Names1 called new names: Table: Names1

Name.	New_names
XXX10.R.999.15.	1099915A
XXX10.C.999.15.	1099915D

Code:

SELECT
    SUBSTRING(Name,7,1) + SUBSTRING(name,9,3) + SUBSTRING(name,13,2) AS new_names
    , CASE
    WHEN SUBSTRING(name,7,1) LIKE 'R' THEN ('A')
    WHEN SUBSTRING(name,7,1) LIKE 'A' THEN ('D')
    END AS directions
    , CONCAT(new_names,direction)
FROM [case1].[case].[example]
INNER JOIN [names1A].[names] ON [names1].[new_names]

• sql
• sql-server
• sql-server-2019

Share Improve this question Follow edited Mar 23 at 20:57 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Mar 23 at 2:37 Inquiring MindInquiring Mind 1933 bronze badges 8

• 6 Sample data and expected results are essential. Your code attempt would be helpful as well. – Charlieface Commented Mar 23 at 3:08
• 1 Nobody here can peer over your shoulder to see the queries on your screen nor the errors that result. Seems like you'd need a persisted computed column on which you could add an index so that joins have half a chance of being efficient. As others have mentioned this kind of question needs a minimal, reproducible example. And if you're experiencing errors then include a copy-paste of the error message(s) - as text, not screen shots. – AlwaysLearning Commented Mar 23 at 5:28
• I have added more context. Sorry about that. – Inquiring Mind Commented Mar 23 at 13:14
• 1 @InquiringMind Open a new question about APPLY with a minimal reproducible example – MatBailie Commented Mar 24 at 7:16
• 1 You just put the same expression you already have in the join? Something like: select * from table1 t1 join table2 T2 on substring_etc(something_or_other_from_table2) = substring_etc(something_or_other_from_table1) – siggemannen Commented Mar 24 at 7:42

 |  Show 3 more comments

3 Answers 3

Sorted by: Reset to default 0

You can use APPLY to 'create' data values 'in order', and reference them in subsequent steps / joins, etc.

CREATE TABLE example (name VARCHAR(32));
CREATE TABLE name    (name VARCHAR(32));

INSERT INTO
   example
VALUES
  ('XXX10.R.999.15'),
  ('XXX10.C.999.15'),
  ('XXX10.Z.999.15')
;

INSERT INTO
   name
VALUES
   ('1099915A'),
   ('1099915D'),
   ('1099915X')
;

6 rows affected

SELECT
   *
FROM
   [example]   AS e
CROSS APPLY
(
   SELECT
      SUBSTRING(e.name,7,1)
)
   AS old_code(value)
CROSS APPLY
(
   SELECT
      CASE old_code.value
         WHEN 'R' THEN 'A'
         WHEN 'C' THEN 'D'
                  ELSE old_code.value
      END
)
   AS new_code(value)
CROSS APPLY
(
   SELECT
      CONCAT(
         SUBSTRING(e.name,  4, 2), 
         SUBSTRING(e.name,  9, 3),
         SUBSTRING(e.name, 13, 2),
         new_code.value
      ) 
)
   AS new_name(value)
FULL OUTER JOIN
   [name]   AS n
      ON n.name = new_name.value

name	value	value	value	name
XXX10.R.999.15	R	A	1099915A	1099915A
XXX10.C.999.15	C	D	1099915D	1099915D
XXX10.Z.999.15	Z	Z	1099915Z	null
null	null	null	null	1099915X

fiddle

Join tables on condition

select t1.Name,t2.Name New_names
from Names1 t1
left join Names1A t2 on 
  t2.Name=concat(substring(t1.name,3,2)
           ,substring(t1.name,8,3),substring(t1.name,12,2)
           ,case substring(t1.name,6,1) 
               when 'R' then 'A' 
               when 'C' then 'D' 
               else 'X' end
           )

Name	New_names
XX10.R.999.15	1099915A
XX10.C.999.15	1099915D

fiddle

Do a translate and replace to eliminate the unnecessary characters. Tack on the final suffix by checking the character in the 6th position.

/* join condition */
on replace(translate(Names1, 'XACDR.', '______'), '_', '') + case substring(Names1, 6, 1) when 'R' then 'A' when 'C' then 'D' else '' end = Names1A

本文标签： sqlMerging substrings from two tables to a joinStack Overflow