Trim field. Remove everything past a certain character

Question

Post reply

Trim field. Remove everything past a certain character

LoosinMaMind

Hall of Fame

Points: 3366
More actions
October 7, 2010 at 7:04 am

#387960

Hi I have the following field in a refernece table ...

ARMPMAEWC : ARMPM

I have the following field in another table which i need to link to using the above key

ARMPMAEWC

How do i trim the top field so i loose every thing after the : ??

This way I can then link the two tables

Thanks in advance

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

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

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 1

Use the combination of charindex and substring function to get the results you want and then perform the linking

http://msdn.microsoft.com/en-us/library/ms186323.aspx

http://msdn.microsoft.com/en-us/library/ms187748.aspx

---------------------------------------------------------------------------------------
It begins by taking the first step.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Leju Geevarghese (10/7/2010)

Use the combination of charindex and substring function to get the results you want and then perform the linking

http://msdn.microsoft.com/en-us/library/ms186323.aspx

http://msdn.microsoft.com/en-us/library/ms187748.aspx

Uh-huh... got some code that might show how to pull off that particular combination including the join? 😉

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

p.stevens76 (10/7/2010)

Hi I have the following field in a refernece table ...

ARMPMAEWC : ARMPM

I have the following field in another table which i need to link to using the above key

ARMPMAEWC

How do i trim the top field so i loose every thing after the : ??

This way I can then link the two tables

Thanks in advance

Here's a way to strip the data as you requested...

DECLARE @SomeString VARCHAR(100)



 SELECT @SomeString = 'ARMPMAEWC : ARMPM'

 SELECT SUBSTRING(@SomeString, 1, CHARINDEX(' :',@SomeString+' :')-1)

The problem is that you shouldn't leave the data that way because it's in a denormalized condition which is going to continue to cause you grief. If you were to post some data (please see the first link in my signature line below for how to do that properly), one of us could show you how to split these off into columns in a table. If you can't modify the table for some reason, let us know that too because there are some high speed work arounds.

Part of the problem of leaving the table denormalized is that you can't use indexes like they should if you always have to calculate the value from the column.

--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)
Intro to Tally Tables and Functions

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 4

LoosinMaMind

Hall of Fame

Points: 3366

October 11, 2010 at 3:11 am

#1234839

Many Thanks for your helps. Have sorted the issue now with the original source file.