LITIGATION MATTER IN RE:1931 BELMONT CONDO ASSN V. LLC (FILE NO. 62-35659)
Can someone assist me in removing (FILE NO. from my description column in my query result to text?
Before running below update in production environment, do it in test environment!
Now, if text always ends with "(FILE NO. .....)" you can do this:
declare @s varchar(100)=' (FILE NO. ';
update yourtable
set yourfield=left(yourfield,charindex(@s,yourfield))
from yourtable
where yourfield like '%'+@s+'%'
;
If "(FILE NO. ....)" can be followed by text you want to keep, you can do this:
declare @s varchar(100)=' (FILE NO. ';
update yourtable
set yourfield=substring(yourfield
,charindex(@s,yourfield)+len(@s)+1
,charindex(') '
,yourfield+' '
,charindex(@s,yourfield)+len(@s)+1
)
-(charindex(@s,yourfield)+len(@s)+1)
)
where yourfield like '%'+@s+'%'
;
Ps.: Running the update is at your own risk, so do intensive test before updating!
Thanks Bitsmed. However, I cannot run any modification script against their database. I can only query the table and send the result to a txt file. if there is a way for me to query that column and omit the (File NO section of the rows that is my ultimate goal.
if text always ends with "(FILE NO. .....)" you can do this:
declare @s varchar(100)=' (FILE NO. ';
select case
when charindex(@s,yourfield)>0
then left(yourfield,charindex(@s,yourfield))
else yourfield
end as yourfield
from yourtable
;
If "(FILE NO. ....)" can be followed by text you want to keep, you can do this:
declare @s varchar(100)=' (FILE NO. ';
select case
when charindex(@s,yourfield)>0
then substring(yourfield
,charindex(@s,yourfield)+len(@s)+1
,charindex(') '
,yourfield+' '
,charindex(@s,yourfield)+len(@s)+1
)
-(charindex(@s,yourfield)+len(@s)+1)
)
else yourfield
end as yourfield
from yourtable
;
below is what I have
declare @s varchar(100)=' (FILE NO. ';
select case
when charindex(@s,description)>0
then left(description,charindex(@s,description))
else description
end as description
from matters
;
This is the message I received.
Msg 8116, Level 16, State 1, Line 3
Argument data type text is invalid for argument 1 of left function.
I belive description is a reserved word, so try changing to:
declare @s varchar(100)=' (FILE NO. ';
select case
when charindex(@s,[description])>0
then left([description],charindex(@s,[description]))
else [description]
end as [description]
from matters
;
I receive the below message
Msg 8116, Level 16, State 1, Line 3
Argument data type text is invalid for argument 1 of left function.
It works perfectly here.
- Which version of Microsoft SQL Server are you running?
- Please provide table definition as create statement
- Please provide sample data as insert statement
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
USE [ProLaw]
GO
/****** Object: Table [dbo].[Matters] Script Date: 7/29/2016 5:24:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Matters](
[Matters] varchar NOT NULL,
[MatterID] varchar NOT NULL,
[ShortDesc] varchar NULL,
[AreaOfLaw] varchar NULL,
[MatterType] varchar NOT NULL,
[OpenedDate] [datetime] NULL,
[ClientSort] varchar NULL,
[MatterIDSort] varchar NULL,
[ClientPronoun] varchar NULL CONSTRAINT [CS_MattersClientPronoun] DEFAULT ('T'),
[OpposingPronoun] varchar NULL CONSTRAINT [CS_MattersOpposingPronoun] DEFAULT ('T'),
[Status] varchar NULL,
[StatusDate] [datetime] NULL,
[Entered] [datetime] NULL,
[Rates] varchar NULL,
[Tasks] varchar NULL,
[StmnFormats] varchar NULL CONSTRAINT [CS_MattersStmnFormats] DEFAULT ('STANDARD'),
[PreBillStmnFormats] varchar NULL CONSTRAINT [CS_MattersPreBillStmnFormats] DEFAULT ('STANDARD'),
[BillFreq] varchar NULL CONSTRAINT [CS_MattersBillFreq] DEFAULT ('Monthly'),
[AddingProfessionals] varchar NULL,
[AddingDateTime] [datetime] NULL,
[FirmPercentage] [numeric](7, 4) NULL CONSTRAINT [DF__Matters__FirmPer__1CF15040] DEFAULT (0),
[BudgetFees] [numeric](12, 2) NULL CONSTRAINT [DF__Matters__BudgetF__1DE57479] DEFAULT (0),
[BudgetHours] [numeric](12, 2) NULL CONSTRAINT [DF__Matters__BudgetH__1ED998B2] DEFAULT (0),
[BudgetHardCosts] [numeric](12, 2) NULL CONSTRAINT [DF__Matters__BudgetH__1FCDBCEB] DEFAULT (0),
[BudgetSoftCosts] [numeric](12, 2) NULL CONSTRAINT [DF__Matters__BudgetS__20C1E124] DEFAULT (0),
[WDFees] varchar NULL,
[WDHardCosts] varchar NULL CONSTRAINT [CS_MattersWDHardCosts] DEFAULT ('N'),
[WDSoftCosts] varchar NULL CONSTRAINT [CS_MattersWDSoftCosts] DEFAULT ('N'),
[PeriodicRetainer] [numeric](12, 2) NULL,
[StmnRTF] varchar NULL,
[EventTypeRules] varchar NULL,
[EventTypeRules2] varchar NULL,
[SecurityGroupView] varchar NULL,
[SecurityGroupChange] varchar NULL,
[SecurityGroupDelete] varchar NULL,
[Description] [text] NULL,
[Notes] [text] NULL,
[IsReverseView] varchar NULL CONSTRAINT [DF__Matters__IsRever__21B6055D] DEFAULT ('N'),
[BillingGroup] [int] NULL,
[ClientID] varchar NULL,
[ClientIDSort] varchar NULL,
[SecurityGroupPortalView] varchar NULL,
[SettlementStmnFormats] varchar NULL,
[SettleTrustAccounts] varchar NULL,
[SettleCashAccounts] varchar NULL,
[WFStatus] [int] NOT NULL DEFAULT (0),
[IsApplySecurity] varchar NULL DEFAULT ('Y'),
[BillFreqCosts] varchar NULL,
[MatterNum] varchar NULL,
[SubMatterNum] varchar NULL,
[ClosingWFStatus] [int] NOT NULL DEFAULT ((-1)),
[ILSKey] varchar NULL,
[ActivityCodeSet] varchar NULL,
[UseActivityCodeForFees] varchar NULL,
[UseActivityCodeForCosts] varchar NULL,
[QOFFICELOCATION] varchar NULL,
[QCOUNTY2] varchar NULL,
PRIMARY KEY CLUSTERED
(
[Matters] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Matters] WITH NOCHECK ADD CONSTRAINT [MattersSecurityGroupPortalView_FK] FOREIGN KEY([SecurityGroupPortalView])
REFERENCES [dbo].[SecurityGroup] ([SecurityGroup])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Matters] CHECK CONSTRAINT [MattersSecurityGroupPortalView_FK]
GO
ALTER TABLE [dbo].[Matters] WITH NOCHECK ADD CONSTRAINT [MattersSettleCashAccounts_FK] FOREIGN KEY([SettleCashAccounts])
REFERENCES [dbo].[Accounts] ([Accounts])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Matters] CHECK CONSTRAINT [MattersSettleCashAccounts_FK]
GO
ALTER TABLE [dbo].[Matters] WITH NOCHECK ADD CONSTRAINT [MattersSettlementStmnFormats_FK] FOREIGN KEY([SettlementStmnFormats])
REFERENCES [dbo].[StmnFormats] ([StmnFormats])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Matters] CHECK CONSTRAINT [MattersSettlementStmnFormats_FK]
GO
ALTER TABLE [dbo].[Matters] WITH NOCHECK ADD CONSTRAINT [MattersSettleTrustAccounts_FK] FOREIGN KEY([SettleTrustAccounts])
REFERENCES [dbo].[Accounts] ([Accounts])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Matters] CHECK CONSTRAINT [MattersSettleTrustAccounts_FK]
GO
ALTER TABLE [dbo].[Matters] WITH NOCHECK ADD CHECK (([BillingGroup] >= 0))
GO
I cannot get the sample data. However, if you would like to do a remote session, i would be able to swing that.
Turns out left function doesn't work with fieldtype text.
So we can either cast the text field as varchar or try with substring function.
declare @s varchar(100)=' (FILE NO. ';
select case
when charindex(@s,[description])>0
then left(cast([description] as varchar(max)),charindex(@s,[description]))
else [description]
end as [description]
from matters
;
I ran the above, but the situation is the same. see below. you see, I have two carriage returns, so if we can remove the two carriage returns then I wouldn't need to remove the (FILE
CORPORATE BUSINESS MATTER IN RE:
GENERAL MATTERS
(FILE NO. 4-30326)
declare @s varchar(100)=' (FILE NO. ';
select case
when charindex(@s,replace(replace(cast([description] as varchar(max)),'\r',''),'\n',''))>0
then left(replace(replace(cast([description] as varchar(max)),'\r',''),'\n',''),charindex(@s,replace(replace(cast([description] as varchar(max)),'\r',''),'\n','')))
else [description]
end as [description]
from matters
;
Still the same issue.
LITIGATION MATTER IN RE:
BINKOWSKI V. HAWORTH
(FILE NO. 62-38099)
this is so frustrating!!!!
I see, this was not the sample you provided in your first post.
You probably want to keep the linefeeds for the first part of the text, so if you change the "declare ...." you should be fine (removed the first space):
declare @s varchar(100)='(FILE NO. ';
select case
when charindex(@s,[description])>0
then left(cast([description] as varchar(max)),charindex(@s,[description]))
else [description]
end as [description]
from matters
;
sorry about this. this is now what i see. some have all removed except the leading ( and some still have the (FILE as shown below.
ESTATE MATTER IN RE:
ESTATE OF LILLIAN TEPLINSKY
(FILE# 62-40007)
PERSONAL INJURY (
Change to:
declare @s varchar(100)='(FILE';
cool, how do you get rid of the leading (
LITIGATION (
Change this line:
then left(cast([description] as varchar(max)),charindex(@s,[description]))
to:
then left(cast([description] as varchar(max)),charindex(@s,[description])-1)
you are the BEST!!!
How do i incorporate your great work into this script to get all the data with your format?
select clientid, ClientSort as ClientName,'Y' as Enabled,'N' as HIPPA, matternum, Description as MatterName,'Y' as Enable, 'N' as HIPPA, areaoflaw from matters order by Clientid, matterid