-1

I have two columns that look like this :

Table_1

termid Nominal Total
1234 75.000.000 1
123 11.432.105.000 61
2345 339.660.000 3
234 199.800.000 2
12345 3.760.079.000 29

Table_2

tid type region locatin merk
00012345 PSW01 Jakarta I JKT1-LANTAMAL HYOSUNG
DTBA234 EDC Jakarta I JKT1-RKB BRI HYOSUNG
00001234 PSW01 Jakarta I JKT1-APOTIK KIMIA FARMA HYOSUNG
EDC2345 EDC Jakarta III JKT1-KPU JAKARTA PUSAT WINCOR
00000123 PSW01 Jakarta I JKT-SPBU CIDENG HYOSUNG

So i want to left join the table with this query :

SELECT *
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2
ON t1.Termid = CAST(t2.tid AS INT)

The query can run perfectly when I exclude the EDC type. But since I want to concatenate the whole line, I'm having an error like:

Conversion failed when converting the varchar value 'DTBA234' to data type int.

I know that the error is because there are characters other than numeric. But I don't know how to solve like the above case.

Can you help me?

Thank you.

** Note : Sorry for my english

GuidoG
  • 11,359
  • 6
  • 44
  • 79
Insan Cahya
  • 61
  • 5
  • 1
    How do you want join the table ? By removing the alphabet in `tid` ? – Squirrel Jul 26 '22 at 05:32
  • 1
    maybe [isnumeric](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver16) can help – GuidoG Jul 26 '22 at 05:32
  • Yes, by removing the alphabet – Insan Cahya Jul 26 '22 at 05:38
  • Does the alphabet only appear in-front of the string or maybe in middle or at the back ? Please show more samples data of such case – Squirrel Jul 26 '22 at 06:10
  • But please when giving a [mre] the input tables should be given as initialization code in code block format in columns. So we & you can cut & paste & run the whole example but also read the input as tables. PS Please clarify via edits, not comments. Give minimal representative data. (Less than now.) PS This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. – philipxy Jul 26 '22 at 06:26
  • The alphabet only appear in front and middle of the string – Insan Cahya Jul 26 '22 at 06:50

2 Answers2

1

You can extract the integer from the tid column of Table_2 and join with the termid column of Table_1.

Assuming the integer value in the tid column will be together, you can do the following:

SELECT *
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2
ON t1.Termid = CAST(SUBSTRING(t2.tid, PATINDEX('%[0-9]%', t2.tid), LEN(t2.tid)) AS INT)

NOTE: If tid has any value like EDC2345DBTA123, this won't work.

Muktadirul Islam
  • 76
  • 4
0

As Sql Server doesnt support regexp_replace like other dbs theres no direct way but alternatively you can use SUBSTRING(id, Patindex...) like below reference :

How to find and remove alphabet letters in in a column on MS sql server

Himanshu
  • 3,830
  • 2
  • 10
  • 29