T-SQL string manipulation

Question

0.00/5 (No votes)

See more:

I have a function that checks for space and 'and' in a table column values and returns the output column values replaced with a underscore. I have written the function but when i am applying it is not removing the 'and' from the string.

My data looks like this:

crop data :

crop name       | translated column name
----------------------------------------
fruits and nuts | crop.group.fruits_nuts

What I have tried:

SQL

CREATE  function dbo.translationkey_v13
(  
   @column_name nchar(15) 
) 
returns nvarchar(1000)  
as  
begin
DECLARE @numletters int;
DECLARE @counter int;
DECLARE @str nvarchar(100);
DECLARE @newcolumn_name nvarchar(100)

    SET @numletters = LEN(@column_name);
    SET @counter = 1;
    SET @newcolumn_name = '';

    WHILE @counter <= @numletters

        BEGIN

            -- a. read next character:
            ----------------------------------------------------------------
            SET @str = LOWER(SUBSTRING(@column_name, @counter, 1));

            
            -- b. check for space:
            ----------------------------------------------------------------
            IF UNICODE(@str) = 32
                BEGIN
                    SET @str = '_';
                END

            -- c. search for and in the string 'and':
            ----------------------------------------------------------------
            IF (@str  like ('% '+ 'and' + ' %'))
                BEGIN 
                    SET @str = REPLACE(@str, '% '+ 'and' + ' %' ,' ')
                END



            SET @newcolumn_name = @newcolumn_name + @str;   
            SET @counter = @counter + 1;

        END

    RETURN CONCAT('crop.groups.',@newcolumn_name)   
END

Posted 9-Sep-21 1:21am

Member 15352290

Updated 9-Sep-21 1:33am

Richard Deeming

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2021-09-09T01:33:00

Solution 1

Your @str variable is a one-character substring. It can never match the condition @str like '%and%', since that would require at least a three-character substring.

Try:

SQL

CREATE OR ALTER FUNCTION dbo.translationkey_v13
(  
   @column_name nchar(15) 
) 
Returns nvarchar(1000)  
As
BEGIN
DECLARE @result nvarchar(1000);
    
    SET @result = @column_name;
    SET @result = Replace(@result, N'and', N'_');
    SET @result = Replace(@result, N' ', N'_');
    
    WHILE @result Like N'%[_][_]%'
    BEGIN
        SET @result = Replace(@result, N'__', N'_');
    END;
    
    Return N'crop.group.' + LOWER(@result);
END;

NB: This won't work well if the input contains "and" as part of a larger word. For example, given the input "sand and cement", the output would be "s_cement".

If you only want to remove "and" between other words, change the first Replace to:

SQL

SET @result = Replace(@result, N' and ', N'_');

Posted 9-Sep-21 1:33am

Richard Deeming

Comments

Member 15352290 9-Sep-21 7:51am

I am trying to run the above function like this:
where new_CropGroups is a table like this:

Beverage and spice crops
Cereals
Fruit and nuts
Leguminous crops
Oilseed crops
Other crops
Root and tuber crops
Sugar crops
Vegetables and melons

and i want my output to be:

crop.group.beverage_spice_crops
crop.group.cereals
crop.group.fruit_nuts
crop.group.leguminous_crops
crop.group.oilseed_crops
crop.group.other_crops
crop.group.root_crops
crop.group.sugar_crops
crop.group.vegetables_melons

Running my function:
DECLARE @sql nvarchar(1000)

SET @sql = (SELECT group_name from new_CropGroups where group_name = 'Beverage and spice crops')

SELECT dbo.translationkey_v14(@sql) as tranlsation_column

Output I got:
translation_column
crop.group.beverage_sp

Richard Deeming 9-Sep-21 8:43am

Your parameter is declared as nchar(15); if you pass in a longer value, such as "Beverage and spice crops" (24 characters), it will be truncated.

If you want to be able to pass in longer values, change the parameter type to match the column type.

Member 15352290 9-Sep-21 7:55am

How I can pass all the attributes of my table in the function??? The way I am doing is accepting only one value I want all the rows to be passed and all the rows to be converted into the way it suppose to convert

Richard Deeming 9-Sep-21 8:44am

SELECT group_name, dbo.translationkey_v13(group_name) As translation_column FROM new_CropGroups

Member 15352290 9-Sep-21 9:23am

Thank you

Member 15352290 9-Sep-21 9:27am

Thank you so much that completely solved my problem

Member 15352290 10-Sep-21 8:04am

I am replacing 'or' in this similar manner as and
SET @result = Replace(@result, N' and ', N'_');
But it is not working
SET @result = Replace(@result, N' or', N'_');
for leaf or vegetables
For example Output is crop.group.vegetables_melons_leafy_or_stem_vegetables

Richard Deeming 10-Sep-21 9:45am

Works fine for me:

SET @result = @column_name;
SET @result = Replace(@result, N' and ', N'_');
SET @result = Replace(@result, N' or ', N'_');
SET @result = Replace(@result, N' ', N'_');
...

Input: leaf or vegetables
Output: crop.group.leaf_vegetables