-1

I am beginner in SQL, please help me... I have a table like this

tblMain

tagid tagname category
37286 A sas device
56789 A Equipment
28676 B sas device
46099 B Equipment
34596 C sas device
77649 C Equipment

tblExtradata

tagid extradata
37286 dType(X),sAddress(1011)
56789 spName(X),cType(Z)
28676 dType(U),sAddress(1013)
46099 spName(V),cType(W)
34596 FX(i41),TX(j56)
77649 FX(i11),TX(j47)

How can I extract my desired data from main table? like table below: Desired data:

tagname sAddress cType
A 1011 Z
B 1013 W

I use substring() and strpos() like below

SELECT tblMain.tagname,
SUBSTRING(tblExtradata.extradata,strpos(tblExtradata.extradata,'sAddress')+9,strpos(SUBSTRING(tblExtradata.extradata,strpos(tblExtradata.extradata,'sAddress')+9,23),')')-1) AS sAddress
FROM tblMain
INNER JOIN tblExtradata ON tblMain.tagid=tblExtradata.tagid

WHERE tblExtradata.extradata LIKE '%sAddress%'

Which results

tagname sAddress
A 1011
B 1013

But How can I get to final desired data?

Hamed Mtd
  • 1
  • 1
  • Welcome to Stack Overflow; please [take the tour](https://stackoverflow.com/tour) which you were asked to when you signed up, read the [question guide](https://stackoverflow.com/help/how-to-ask) and its linked resources and please do not post [images](https://meta.stackoverflow.com/questions/285551) of code or data. – Stu Aug 16 '23 at 21:12
  • So what is the relationship between Address and cType, because it seems to me that they are on different rows with different ids – Nnaemeka Daniel John Aug 18 '23 at 21:00

0 Answers0