Related to string manipulation

Question

I have a table of crop which contain a column with crop name For Example Fruit and Nuts, Vegetables and Melons. I have to use T-SQL and remove the 'and' insert the spaces with a underscore and a text crop.groups should be added in front. For example Fruit and Nuts will become crop.groups.fruit_nuts. I have to to do these for all the crops together in SQL. When we run the query a separate column with these transformation should be generated. Also I have to create a function doing all this to me.

My Function:

CREATE  function dbo.translationkey_v10
(  
   @column_name nchar(15) 
) 
    
returns nvarchar(1000)  
as  
begin

    DECLARE @numletters int;
    DECLARE @counter int;
    DECLARE @str nvarchar(6);
    DECLARE @newcolumn_name nvarchar(50)
    


        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. search for and in the string 'and':
                ----------------------------------------------------------------
                IF (@str LIKE '%and%')
                    BEGIN 
                        SET @str = REPLACE(@str,' and','')
                    END

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


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

            END

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

Sample data

Crop name : Beverages and spice crops

Translation output : crop.group.beverages_spice_crops

So you want to select all the distinct crop names and join them with an underscore? Does this help: https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv The accepted answer is T-SQL This page also shows the same thing: https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/ — Jerry Jeremiah, Sep 09 '21 at 08:31
So what have you tried so far and what are you having trouble with? — Stu, Sep 09 '21 at 08:31
Please try to use formatting to make it clear what is data and was is part of your sentence. For example... **I have to use T-sql and remove the `'and'` and insert the spaces with a underscore and a text `'crop.groups'` should be added in front.** *(Or, even better, include a sample data set, showing the input and the expected output.)* — MatBailie, Sep 09 '21 at 08:41

score 1 · Answer 1 · answered Sep 09 '21 at 08:34

1

This would seem very simple using replace:

select cropname, Concat('crop.groups.', Replace(cropname, ' and ','_'))
from crop

answered Sep 09 '21 at 08:34

Stu

30,392
6
14
33

I have to create a function that does all this for me. – anam Sep 09 '21 at 09:13
I tried replacing the way you did but I am not able to do – anam Sep 09 '21 at 09:14
1

@anam You have not mentioned requiring a function in your question, this answers your question as written; if you are having trouble implementing a function, that's a different problem deserving of its own question. – Stu Sep 09 '21 at 09:16

Related to string manipulation

1 Answers1