Update statement to trim certain values

Transact-SQL
1

UPDATE #Tables
set value = T3.data

from #Table1 T1 
JOIN #Table2 T2 ON T1.Tid = T2.Tid
JOIN Tables3 T3 ON T3.Tid1 = T2.Tid1

The Data coming from T3.Data will be like in format

Abcdefghi^4|2344

From this I need to get displayed on 4

Or

Abcdefghi^41|234
From this I need to get displayed only 41

Or

Abcdefghi^410|234433
From this I need to get displayed only 410

Bacically I need to get only data displayed between ^ and |

Abcdefghi^ will be same but length of data after that will differ

Can anyone suggest the query to be written for this please in MS SQL

2

So how are #Tables joined to the other tables?

3

One of the most important and advantageous things that you can do with any computer language is to know what all the different intrinsic functions actually can do and how to use most of them. Here's a link to get you started.
https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-2017

Here's an example of your problem and solution as a SELECT. The VALUES part is simulating the "T3" aliased table from your code. You should be able to apply the formula and the CROSS apply to your real UPDATE code fairly easily. And memorize how to use both CROSS APPLY and CHARINDEX because they can be real life savers in a whole lot of problems.

 SELECT  OriginalString = t3.data
        ,DesiredString  = SUBSTRING(t3.data,pos.StartPos,pos.EndPos-pos.StartPos+1)
   FROM (VALUES
         ('Abcdefghi^4|2344')
        ,('Abcdefghi^41|234')
        ,('Abcdefghi^410|234433')
        ,('SomeOtherStringThatsNotTheSameSize^14359212|WhatEverDoesntMatter')
        ,('Short^A2|B')
        )t3(data)
  CROSS APPLY (SELECT StartPos = CHARINDEX('^',t3.data)+1, EndPos = CHARINDEX('|',t3.data)-1)pos
;
1 Like