[AnalyticLunatic]

Hello Everyone, I seem to be stuck and could use some assistance from someone more experienced in SQL than myself

Here is the situation:

I have a crystal report in my VB.NET solution that gets generated for a user when they click a certain button. I use a conditional to determine which .fill statement of the reports source tableadapter to use to fill the adapter before the report opens.

In each record for the tableadapter, there is a field called 'ConDispText' which at the start of the string has 2 brackets containing a location code (Ex. [AG] or [1003]). I need to change my .fill statement on the tableadapter to remove or replace the location code ([AG], [1003], or similar) so that it no longer shows up as part of the ConDispText on the crystal report.

After some research it looks like the REPLACE() function is what I'm after, but I cannot figure out how to remove everything between the brackets, not just replace the brackets with empty strings.

What I can do: [AG] The Code Description = AG The Code Description // Replaces the brackets with empty strings
What I need to do: [AG] The Code Description = The Code Description // Removes brackets and everything between.

I cannot just replace say characters 1-4, as some of the codes have 2 characters between brackets, some have up to 5.

Any help would be greatly appreciated as I'm still a pretty low-level novice when it comes to Crystal Reports or SQL.

Thanks!
-A.L.

[Atli]

I don't know much about Crystal Reports, but as far as SQL databases are concerned, storing multiple pieces of data inside a single field, like you are describing there, is generally extremely bad. This exact situation is one of the main reasons why.

With that said, you should be able to trim anything between the start of the field and up until the character after the first occurrence of the first "]" char by doing something like this:

SELECT
    SUBSTRING(
        ConDispText, 
        CHARINDEX(']', ConDispText) + 1, 
        LEN(ConDispText)
    ) AS ConDispText
FROM ...

This is untested, but should do the trick.

[AnalyticLunatic]

Atli, on 22 October 2012 - 07:48 AM, said:

I don't know much about Crystal Reports, but as far as SQL databases are concerned, storing multiple pieces of data inside a single field, like you are describing there, is generally extremely bad. This exactly situation is one of the main reasons why.

With that said, you should be able to trim anything between the start of the field and up until the character after the first occurrence of the first "]" char by doing something like this:

SELECT
    SUBSTRING(
        ConDispText, 
        CHARINDEX(']', ConDispText) + 1, 
        LEN(ConDispText)
    ) AS ConDispText
FROM ...

This is untested, but should do the trick.

Thank you so much for the reply Atli!

It looks as if your provided example will do the trick for what I'm after If it is not too much trouble, would you mind or be able to break this down in English for my own understanding in the future? I just want to ensure if I am understanding the logic correctly.

As for the design behind the field, I understand this is simply something I was put onto during downtime on another project. The ConDispText field is basically a field combining a Location Code, Description, Cost Calculation, and overall Cost in one string. I believe it is generated the same instance the other fields are updated/calculated.

Again, thank you so much!

This post has been edited by AnalyticLunatic: 22 October 2012 - 08:29 AM

[Atli]

AnalyticLunatic, on 22 October 2012 - 03:01 PM, said:

If it is not too much trouble, would you mind or be able to break this down in English for my own understanding in the future?

Sure.

The SUBSTRING function retrieves a part of a string. The first parameter is the string itself, the second the first index to read and the third the number of characters to read. Using the CHARINDEX function I located the index of the first occurrence of the "]" char, and passed that into the second parameter of the SUBSTRING function. Then I just passed the total length of the string into the third parameter using the LEN function.

So, in pseudo code (sort of):

SELECT 
    a part of (
        ConDipText,  
        from the first index of ']' plus one,
        until the end of ConDipText
    ) as ConDispText

[AnalyticLunatic]

Atli, on 22 October 2012 - 08:28 AM, said:

AnalyticLunatic, on 22 October 2012 - 03:01 PM, said:

If it is not too much trouble, would you mind or be able to break this down in English for my own understanding in the future?

Sure.

The SUBSTRING function retrieves a part of a string. The first parameter is the string itself, the second the first index to read and the third the number of characters to read. Using the CHARINDEX function I located the index of the first occurrence of the "]" char, and passed that into the second parameter of the SUBSTRING function. Then I just passed the total length of the string into the third parameter using the LEN function.

So, in pseudo code (sort of):

SELECT 
    a part of (
        ConDipText,  
        from the first index of ']' plus one,
        until the end of ConDipText
    ) as ConDispText

Ah, thank you! I had that a little twisted in my own head now I get it!