Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PCDUNN
Starting Member
2 Posts |
 Posted - 2012-02-16 : 20:33:23
|
| If I have a series of data that I select from table 'A' that has a format 123, 456, 789... and a series of data from table 'B' with a format (static prefix) ABC_123, ABC_456, ABC_789...how can I truncate the data in the select from table 'B' soi I have the same data for a match? I.e, 'A' = 123..., 'B' = 123...I've seen methods that include updates, views and various other methods, but being new, not sure the best.PCD |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-02-16 : 21:03:45
|
if the data in B is always in consistent format, use substring() or right() to get the string that you wantsubstring(B.Column, 4, len(B.Column) - 4)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
PCDUNN
Starting Member
2 Posts |
Posted - 2012-02-17 : 12:11:37
|
| It worked...thanks!SELECT TL.EXT_TAX_LOT_ID,substring(EXT_TAX_LOT_ID, 5, len(EXT_TAX_LOT_ID) - 4),SEC.ext_sec_id AS ticker, TL.ACCT_CD AS Portfolio, TL.CUSTODIAN AS Custodian, TL.LONG_SHT_CD, ISNULL(TL.QTY, 0) AS QTYFROM TS_TAX_LOT TL join dbo.CSM_SECURITY SEC on SEC.SEC_ID = TL.SEC_IDWHERE(ISNULL(TL.QTY, 0) != 0)PCD |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-17 : 13:06:11
|
| if its all ABC_ wont REPLACE(Col,'ABC_','') suffice?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Modifying Data Returned on a Select