Update to trim certain values

Question

Update to trim certain values

Thaneesh

Grasshopper

Points: 16
More actions
July 21, 2018 at 3:31 am

#373788

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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 ;

Since you're new to this forum, I also recommend that you read the two "How to" articles at the links in my signature line below under "Helpful Links" to help us help you better and faster on future posts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

An alternative to Jeff's fine solution
😎

USE TEEST; GO SET NOCOUNT ON;

SELECT t3.data AS ORIGINAL_INPUT ,PARSENAME(REPLACE(PARSENAME(REPLACE(t3.data,'^','.'),1),'|','.'),2) AS DESIRED_VALUE FROM (VALUES ('Abcdefghi^4|2344') ,('Abcdefghi^41|234') ,('Abcdefghi^410|234433') ,('SomeOtherStringThatsNotTheSameSize^14359212|WhatEverDoesntMatter') ,('Short^A2|B') )t3(data) ;

Haven't tested the performance difference but looking at the execution plans, I would expect it to be very similar.

andycadley SSCertifiable Points: 5296 More actions · Answer 3

I'd generally recommend avoiding using PARSENAME in that fashion, even though it might seem to work. It does a bunch of things you might not expect, for things like handling square brackets and will return NULL in cases where the parsed string won't end up a valid SQL identifier. Such as:
select parsename('dbo.[Numbers',1)
I've had to fix similar code issues with other functions really only ever meant for specifically handling SQL object names such as QUOTENAME, where they "sort of" work a lot of the time.