[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Delete all occurences from string up to a certain character

Posted on 2013-06-16
5
Medium Priority
?
446 Views
Last Modified: 2013-07-22
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_PORecNotCosted],(0)))
   +isnull([c_TranRecNotCosted],(0)))-isnull([d_TranShipNotcosted],(0)))-isnull([e_OrdRelNotInv],(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?
0
Comment
Question by:jnordeng
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 39251668
Here's what I've got for you.  I assume you'll be using a field name rather than a variable.  I had to escape a single quote (for "don't") to get it to work as a string.

DECLARE @string VARCHAR(1000) = '';
SET @string = 'FEE FII FO FUM THe packers rule AS ((((isnull([QtyOnhand],(0))+isnull([b_PORecNotCosted],(0)))+isnull([c_TranRecNotCosted],(0)))-isnull([d_TranShipNotcosted],(0)))-isnull([e_OrdRelNotInv],(0))), But the Vikings don''t and never have';
-- Check for the ISNULL strings
IF (CHARINDEX('+ISNULL', @string) > 0
    OR CHARINDEX('-ISNULL', @string) > 0)
BEGIN
    SELECT
             -- Grab string up to where ' AS ' appears in the string
             SUBSTRING(@string
                 , 0
                 , CHARINDEX(' AS ', @string)
                 )
            -- Grab string from the *last* comma to the end
            + SUBSTRING(@string
                , DATALENGTH(@string) - (CHARINDEX(',', REVERSE(@string))-1)
                , DATALENGTH(@string)
                )
END
GO

Open in new window

0
 

Author Comment

by:jnordeng
ID: 39251761
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(StartPosition, findposition - 1)
            Debug.WriteLine(tmpstr)
            If CDbl(tmpstr.IndexOf("ISNULL").ToString.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_PORecNotCosted],(0)))
   +isnull([c_TranRecNotCosted],(0)))-isnull([d_TranShipNotcosted],(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_PORecNotCosted],(0)))+isnull([c_TranRecNotCosted],(0)))-isnull([d_TranShipNotcosted],(0)))-isnull([e_OrdRelNotInv],(0)))-isnull([f_EntOrders],(0)))+isnull([g_entPO],(0)))-isnull([h_TranEntOut],(0)))+isnull([i_TranEntIn],(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',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CHISM_icwhse', @level2type=N'COLUMN',@level2name=N'SalesQtyYTD' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'From BCP Steps' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CHISM_icwhse', @level2type=N'COLUMN',@level2name=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_MorningOnHand] DEFAULT ((0)) FOR [QtyOnHandMorning] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_b_POReceivedNotCosted] DEFAULT ((0)) FOR [b_PORecNotCosted] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_c_TranRecNotCosted] DEFAULT ((0)) FOR [c_TranRecNotCosted] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_d_TranShipNotCosted] DEFAULT ((0)) FOR [d_TranShipNotCosted] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_e_OrdRelNotInv] DEFAULT ((0)) FOR [e_OrdRelNotInv] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_f_EntOrders] 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_TranEntOut] DEFAULT ((0)) FOR [h_TranEntOut] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_i_TranEntIn] DEFAULT ((0)) FOR [i_TranEntIn] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesQtyYTD] DEFAULT ((0)) FOR [SalesQtyYTD] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesQtyLY] DEFAULT ((0)) FOR [SalesQtyLY] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtYTD] DEFAULT ((0)) FOR [SalesAmtYTD] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtLY] DEFAULT ((0)) FOR [SalesAmtLY] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_CostAmtYTD] 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_SalesQtyL2] DEFAULT ((0)) FOR [SalesQtyL2] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesQtyL3] DEFAULT ((0)) FOR [SalesQtyL3] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtL2] DEFAULT ((0)) FOR [SalesAmtL2] GO ALTER TABLE [dbo].[CHISM_icwhse] ADD CONSTRAINT [DF_CHISM_icwhse_SalesAmtL3] 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_PhysicalCount] DEFAULT ((0)) FOR [PhysicalCount] GO
0
 

Accepted Solution

by:
jnordeng earned 0 total points
ID: 39251787
Its a hack job. I am going to have to make a database call to get the database type and add it in here. But this does work.

ReplaceCalculatedColumns(ByVal sql As String)
        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 - findposition
            End If
            Dim tmpstr As String = sql.Substring(StartPosition, (findposition - (StartPosition - 6)))
            Debug.WriteLine(tmpstr)
            If CDbl(tmpstr.IndexOf(") NULL").ToString.ToUpper) - 1 > -1 And (tmpstr.IndexOf("+") - 1 > -1 Or tmpstr.IndexOf("-") - 1 > -1) Then
                sql = sql.Replace(tmpstr, "")
            End If

        Loop

    End Sub
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39251813
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.
0
 

Author Closing Comment

by:jnordeng
ID: 39345462
Though comments were offered, in working through it found a solution that worked well enough.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question