jnordeng
asked on
Delete all occurences from string up to a certain character
I need to remove all references from a string that start off with
AS
up to the point of the next ","
if they have "ISNULL" and a plus (+) or minus (-) sign in them.
For example this is part of the string
"FEE FII FO FUM THe packers rule AS ((((isnull([QtyOnhand],(0) )+isnull([ b_PORecNot Costed],(0 )))
+isnull([c_TranRecNotCoste d],(0)))-i snull([d_T ranShipNot costed],(0 )))-isnull ([e_OrdRel NotInv],(0 ))), But the Vikings don't and never have
This string should look like at the end:
FEE FII FO FUM THe packers rule, But the Vikings don't and never have
Can anyone help?
AS
up to the point of the next ","
if they have "ISNULL" and a plus (+) or minus (-) sign in them.
For example this is part of the string
"FEE FII FO FUM THe packers rule AS ((((isnull([QtyOnhand],(0)
+isnull([c_TranRecNotCoste
This string should look like at the end:
FEE FII FO FUM THe packers rule, But the Vikings don't and never have
Can anyone help?
ASKER
basically what we are trying to do here is eliminate the calculated columns out of a series of SQL creation scripts. I am posting code that is not working and then the actual SQL statement below that. Thank you for your help nemws1.
Dim i As Integer
Do
If sql.IndexOf("+") - 1 = -1 And sql.IndexOf("-") - 1 = -1 Then
Exit Do
End If
Dim StartPosition As Integer = sql.IndexOf("AS ")
Dim findposition As Integer = sql.IndexOf("NULL,", StartPosition)
If findposition - 1 = -1 Then
findposition = sql.Length - StartPosition
End If
Dim tmpstr As String = sql.Substring(StartPositio n, findposition - 1)
Debug.WriteLine(tmpstr)
If CDbl(tmpstr.IndexOf("ISNUL L").ToStri ng.ToUpper ) - 1 > -1 And (tmpstr.IndexOf("+") - 1 > -1 Or tmpstr.IndexOf("-") - 1 > -1) Then
sql = sql.Replace(tmpstr, "")
End If
Loop
USE [Tech] GO /****** Object: Table [dbo].[CHISM_icwhse] Script Date: 06/12/2013 16:04:52 ******/
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[CHISM_icwhse1](
[ProductId] [varchar](18) NOT NULL, [WhseId] [int] NOT NULL, [QtyOnHand] [numeric](18, 2) NOT NULL,
[QtyOnHandLastChanged] [smalldatetime] NULL, [QtyOnHandMorning] [numeric](18, 2) NOT NULL,
[b_PORecNotCosted] [numeric](18, 2) NULL, [c_TranRecNotCosted] [numeric](18, 2) NULL,
[d_TranShipNotCosted] [numeric](18, 2) NULL, [e_OrdRelNotInv] [numeric](18, 2) NULL,
---------
[Tot_WhseOnHand] AS ((((isnull([QtyOnhand],(0) )+isnull([ b_PORecNot Costed],(0 )))
+isnull([c_TranRecNotCoste d],(0)))-i snull([d_T ranShipNot costed],(0 )))
-isnull([e_OrdRelNotInv],( 0))),
[f_EntOrders] [numeric](18, 2) NULL,
------------------------
[g_EntPO] [numeric](18, 2) NULL,
[h_TranEntOut] [numeric](18, 2) NULL, [i_TranEntIn] [numeric](18, 2) NULL,
[LongShort] AS ((((((((isnull([QtyOnhand] ,
(0))+isnull([b_PORecNotCos ted],(0))) +isnull([c _TranRecNo tCosted],( 0)))-isnul l([d_TranS hipNotcost ed],(0)))- isnull([e_ OrdRelNotI nv],(0)))- isnull([f_ EntOrders] ,(0)))+isn ull([g_ent PO],(0)))- isnull([h_ TranEntOut ],(0)))+is null([i_Tr anEntIn],( 0))), [SalesQtyYTD] [numeric](18, 2)
NULL, [SalesQtyLY] [numeric](18, 2) NULL, [SalesAmtYTD] [numeric](18, 2) NULL, [SalesAmtLY] [numeric](18, 2) NULL, [CostAmtYTD] [numeric](18, 2) NULL, [CostAmtLY] [numeric](18, 2) NULL, [SalesQtyL2] [numeric](18, 2) NULL, [SalesQtyL3] [numeric](18, 2) NULL, [SalesAmtL2] [numeric](18, 2) NULL, [SalesAmtL3] [numeric](18, 2) NULL, [CostAmtL2] [numeric](18, 2) NULL, [CostAmtL3] [numeric](18, 2) NULL, [PhysicalCount] [numeric](18, 2) NULL, [LastPhysicalCount] [smalldatetime] NULL, [modified_by] [varchar](50) NULL, [modified_date] [datetime] NOT NULL, [Record_Id] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, CONSTRAINT [PK_CHISM_icwhse] PRIMARY KEY NONCLUSTERED ( [ProductId] ASC, [WhseId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'From BCP Steps' , @level0type=N'SCHEMA',@lev el0name=N' dbo', @level1type=N'TABLE',@leve l1name=N'C HISM_icwhs e', @level2type=N'COLUMN',@lev el2name=N' SalesQtyYT D' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'From BCP Steps' , @level0type=N'SCHEMA',@lev el0name=N' dbo', @level1type=N'TABLE',@leve l1name=N'C HISM_icwhs e', @level2type=N'COLUMN',@lev el2name=N' SalesQtyLY ' GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_QtyOnHand ] DEFAULT ((0)) FOR [QtyOnHand] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_MorningOn Hand] DEFAULT ((0)) FOR [QtyOnHandMorning] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_b_PORecei vedNotCost ed] DEFAULT ((0)) FOR [b_PORecNotCosted] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_c_TranRec NotCosted] DEFAULT ((0)) FOR [c_TranRecNotCosted] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_d_TranShi pNotCosted ] DEFAULT ((0)) FOR [d_TranShipNotCosted] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_e_OrdRelN otInv] DEFAULT ((0)) FOR [e_OrdRelNotInv] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_f_EntOrde rs] DEFAULT ((0)) FOR [f_EntOrders] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_g_EntPO] DEFAULT ((0)) FOR [g_EntPO] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_h_TranEnt Out] DEFAULT ((0)) FOR [h_TranEntOut] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_i_TranEnt In] DEFAULT ((0)) FOR [i_TranEntIn] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesQtyY TD] DEFAULT ((0)) FOR [SalesQtyYTD] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesQtyL Y] DEFAULT ((0)) FOR [SalesQtyLY] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtY TD] DEFAULT ((0)) FOR [SalesAmtYTD] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtL Y] DEFAULT ((0)) FOR [SalesAmtLY] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_CostAmtYT D] DEFAULT ((0)) FOR [CostAmtYTD] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_CostAmtLY ] DEFAULT ((0)) FOR [CostAmtLY] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesQtyL 2] DEFAULT ((0)) FOR [SalesQtyL2] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesQtyL 3] DEFAULT ((0)) FOR [SalesQtyL3] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtL 2] DEFAULT ((0)) FOR [SalesAmtL2] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtL 3] DEFAULT ((0)) FOR [SalesAmtL3] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_CostAmtL2 ] DEFAULT ((0)) FOR [CostAmtL2] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_CostAmtL3 ] DEFAULT ((0)) FOR [CostAmtL3] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_PhysicalC ount] DEFAULT ((0)) FOR [PhysicalCount] GO
Dim i As Integer
Do
If sql.IndexOf("+") - 1 = -1 And sql.IndexOf("-") - 1 = -1 Then
Exit Do
End If
Dim StartPosition As Integer = sql.IndexOf("AS ")
Dim findposition As Integer = sql.IndexOf("NULL,", StartPosition)
If findposition - 1 = -1 Then
findposition = sql.Length - StartPosition
End If
Dim tmpstr As String = sql.Substring(StartPositio
Debug.WriteLine(tmpstr)
If CDbl(tmpstr.IndexOf("ISNUL
sql = sql.Replace(tmpstr, "")
End If
Loop
USE [Tech] GO /****** Object: Table [dbo].[CHISM_icwhse] Script Date: 06/12/2013 16:04:52 ******/
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[CHISM_icwhse1](
[ProductId] [varchar](18) NOT NULL, [WhseId] [int] NOT NULL, [QtyOnHand] [numeric](18, 2) NOT NULL,
[QtyOnHandLastChanged] [smalldatetime] NULL, [QtyOnHandMorning] [numeric](18, 2) NOT NULL,
[b_PORecNotCosted] [numeric](18, 2) NULL, [c_TranRecNotCosted] [numeric](18, 2) NULL,
[d_TranShipNotCosted] [numeric](18, 2) NULL, [e_OrdRelNotInv] [numeric](18, 2) NULL,
---------
[Tot_WhseOnHand] AS ((((isnull([QtyOnhand],(0)
+isnull([c_TranRecNotCoste
-isnull([e_OrdRelNotInv],(
[f_EntOrders] [numeric](18, 2) NULL,
------------------------
[g_EntPO] [numeric](18, 2) NULL,
[h_TranEntOut] [numeric](18, 2) NULL, [i_TranEntIn] [numeric](18, 2) NULL,
[LongShort] AS ((((((((isnull([QtyOnhand]
(0))+isnull([b_PORecNotCos
NULL, [SalesQtyLY] [numeric](18, 2) NULL, [SalesAmtYTD] [numeric](18, 2) NULL, [SalesAmtLY] [numeric](18, 2) NULL, [CostAmtYTD] [numeric](18, 2) NULL, [CostAmtLY] [numeric](18, 2) NULL, [SalesQtyL2] [numeric](18, 2) NULL, [SalesQtyL3] [numeric](18, 2) NULL, [SalesAmtL2] [numeric](18, 2) NULL, [SalesAmtL3] [numeric](18, 2) NULL, [CostAmtL2] [numeric](18, 2) NULL, [CostAmtL3] [numeric](18, 2) NULL, [PhysicalCount] [numeric](18, 2) NULL, [LastPhysicalCount] [smalldatetime] NULL, [modified_by] [varchar](50) NULL, [modified_date] [datetime] NOT NULL, [Record_Id] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, CONSTRAINT [PK_CHISM_icwhse] PRIMARY KEY NONCLUSTERED ( [ProductId] ASC, [WhseId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm doing almost the same exact thing in SQL that you are doing in Visual Basic. If your solution is working, I would go with it! SQL is actually really bad for string manipulation, so if you have a working solution, use it.
ASKER
Though comments were offered, in working through it found a solution that worked well enough.
Open in new window