SQL query to find substring in a column and remove certain character

I have a requirement to find substring using certain keyword based on the result i need to remove certain character in SQL

Eg. I have a column called component...I need to extract the values after this string:

"^BY5,3.0,133^FO73,324^B2N,,N,N,N^FV"

Component column values:

^FO41,18^A0N,46,49^FDCAT.NO.^FS^BY5,3.0,133^FO73,324^B2N,,N,N,N^FV23 MAJOR SX^FS
^FO41,18^A0N,46,49^FDCAT.NO.^FS^BY5,3.0,133^FO73,324^B2N,,N,N,N^FVTest Value for 34 Inch^FS,N

The below query is working and giving me the half results:

SELECT 
    SUBSTRING(component, CHARINDEX('^BY5,3.0,133^FO73,324^B2N,,N,N,N^FV',component) + LEN('^BY5,3.0,133^FO73,324^B2N,,N,N,N^FV'), LEN(component)) AS component 
FROM 
    Equipments 
WHERE 
    Type = 'CA'

Result:

23 MAJOR SX^FS
Test Value for 34 Inch^FS,N

Again I need to remove everything after ^, so my result should have the correct values

23 MAJOR SX
Test Value for 34 Inch

The below query is giving me the correct result as "23 Major SX" but it is for only one row. How can I merge these two queries so I can get the values for column which has more than 15 records

SELECT LEFT("23 MAJOR SX^FS", CHARINDEX('^', "23 MAJOR SX^FS") - 1)

Solution

You could break up the second SUBSTRING portion into a CTE to get the remaining text required without having to overcomplicate your first SUBSTRING while trying to remove the ^ characters.

declare @equipment table (component varchar(200)) 
insert into @equipment 
values ('^FO41,18^A0N,46,49^FDCAT.NO.^FS^BY5,3.0,133^FO73,324^B2N,,N,N,N^FV23 MAJOR SX^FS'),
('^FO41,18^A0N,46,49^FDCAT.NO.^FS^BY5,3.0,133^FO73,324^B2N,,N,N,N^FVTest Value for 34 Inch^FS,N')

declare @searchString varchar(50) = '^BY5,3.0,133^FO73,324^B2N,,N,N,N^FV'

;with cte
as (
    select SUBSTRING(component, CHARINDEX(@searchString,component) + LEN(@searchString), LEN(component)) AS component 
    from @equipment
    )
select substring(component,1,CHARINDEX('^',component)-1)
from cte