Finding the columns that contain the word 'kg' (the kg is not gonna be part of another sentence or wod) RRS feed

  • Question

  • Hello everyone! I am a BA student and I have SQL course in my studies, as part of my weekly homework I got asked to find the the only columns  that contain the word 'kg' I know that I use where function but the thing is I got asked to find the sentences that contain the word 'kg' and the word shall not be part of another word for example like kgs. . I hope you understand me.Sorry for the bad English.Thanks!
    Sunday, April 21, 2019 4:57 PM


  • I hope that you somehow have misunderstood the task, because it does not sound like a task for a beginner.

    There are two problems. You are asked to query several columns to see which one(s) that may include the work "kg". This is not a particularly meaningful operation in a well-designed database, because each column in a table is supposed to model a unique attribute of the entity the table models. It is certainly true that far from all databases in this world are well-designed. But still I hope that in a class, you work only with well-modelled databases.

    The next problem is finding the word kg and nothing else. This is certainly not a beginner's task. There are three options:

    1) LIKE. You can easily say WHERE col LIKE '%kg%', but the you will get hits on "luckgiver" and whatnots. Writing the LIKE patters so that you only trap the full word is not fun, since you need to consider punctuation, that the word is first or last in the column etc.

    2) Full-text. Full-text is an additional feature in SQL Server, but very powerful. (It's additional in the sense that by default it is not installed when you install SQL Server, but you can opt to add it, and there is no extra cost for this.) Full-text is designed to find whole words, to hat you can find "old" without having to bother about "bold". However, full-text will find inflected forms, so a search for "kg" will also get a match on "kgs", if the index is built for English. This can be addressed at least to some extend by selecting the language as 0 to get a language-neutral index. All that said, there is still the problem, that you would not build a full-text index for a one-off search. And it has to count as an advanced feature.

    3) Regular expressions. Writing a regular expression that matches the word "kg" and nothing else is simple. However, there is no built-in support for this in T-SQL. You can write a CLR function for the task, and in SQL 2017 you can also invoke a Python script to use regexps. But again we are talking advanced features that I would not expect to be covered in an introductory class.

    I should add here that I have only considered solutions in SQL Server, since this is the topic for this forum, and only questions related to SQL Server are expected here. But it hss happened before that people have posted questions here relating to other platforms. If that would apply to you, you should find a forum for your product. Maybe that platform has a solution for you that does not count as advanced.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, April 21, 2019 6:37 PM