Format database string column

Question

I need to query the database row results is about 300,000 rows (growing).

The call is made from a controller in my main view ( MVC .net core) One of the columns comes in this format:

[ ] The cat is black
[ ] here is the change
[G ] Some other text

I need to strip the [ ] or [G ]

Would I removed it at database level eg:

SELECT [Id]
      ,[Name]
      ,[Text] =  Right([Text], LEN([Text]) - 8) 
FROM MyDatabase

This seems incorrect – although it works – I have to put a magic number 8. What if this changes in the future? Developers insist in not having numbers that have no meaning?

Or do it in the controller :

viewModel.List = data.Select(val => new ViewModel
                    {
                        Id = val. Id,
                        Name = val.Name,
                        Text = val.Text.Substring(val.Text.IndexOf(']'))
                    }).ToList();

If you can shade some light or have a better idea I would appreciate it.

This is very complicated as you might not know what happens in the future with the table or the code. I would recommend cleaning up the table, and work your magic when data gets inserted, unless those are not garbage — Jaques
– Jaques, Commented Feb 3, 2020 at 10:25
Thank you - but I cannot clear the database table as data is important. I have no access on how data or when is inserted. — Emma Brown
– Emma Brown, Commented Feb 3, 2020 at 10:29
I said clean up the data, not clear the data. If the data is important how can you clean it in a query or controller? — Jaques
– Jaques, Commented Feb 3, 2020 at 10:40
Ok sorry - when data is displayed on the view they dont want to see [G] or [] as it is not relevant anymore. — Emma Brown
– Emma Brown, Commented Feb 3, 2020 at 10:43
Understood. The question remain, is this data relevant? (referring to the [] or [G] Does it have to stay in the database. If so. Will the way this is stored change in the future? Can these characters be somewhere else in the text? — Jaques
– Jaques, Commented Feb 3, 2020 at 10:46

Zohar Peled · Accepted Answer · 2020-02-03 10:24:42Z

3

Based on your usage of = and [] It's a pretty safe guess that the database you're using is SQL Server.
In that case, you could do it in the database with charindex - instead of 8 use charindex(']', [Text]):

SELECT [Id]
      ,[Name]
      ,[Text] =  RIGHT([Text], LEN([Text]) - CHARINDEX(']', [Text])) 
FROM MyDatabase

answered Feb 3, 2020 at 10:24

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Emma Brown Over a year ago

Yes sorry - I am using SQL server

Jaques Over a year ago

@Zohar the problem will remain, if there is data that did not start with a [, but there might be a [ in the data somewhere else, it will remove relevant data that is not intentional. Doing this is dangerous, and should be avoided.

Zohar Peled Over a year ago

@Jaques this can be avoided using all kind of techniques, but the OP didn't specify data without the [] or [G ], so...

Emma Brown Over a year ago

I will try this solution, will let you know if it gets approved. Thanks

Emma Brown Over a year ago

Thank you - it got approved :-)

Slai · Accepted Answer · 2020-02-03 12:48:23Z

1

Safer alternative with REPLACE :

SELECT [Id],
       [Name],
       [Text] = REPLACE(REPLACE([Text], '[] ', ''), '[G ] ', '') 
FROM MyDatabase

or probably a bit more efficient in C# :

Text = val.Text.Replace("[] ", "").Replace("[G ] ", "")

answered Feb 3, 2020 at 12:48

Slai

23k5 gold badges49 silver badges55 bronze badges

Collectives™ on Stack Overflow

Format database string column

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related