两道take home SQL题

1. write a function to capitalize teh first letter of a word in a given string;
Example: inicap(UNITED States of AmERica)=United States of America

2. write a procedure to split columns into rows using a delimiter
schema
Id  Name
_______
1 smith
2 Julio|Jones|Falcons
3 White|snow
...
Row 3 would look like >>"3, White", "3, Snow"

我查到的function
# in a function
CREATE FUNCTION [dob].initcap(@String varchar(max))
RETURNS varchar[max( AS)
BEGIN   
    SET @string = LOWER(@string)
    DECLARE @i INT
..     SET @i = ASCII('a')
    WHILE @i <= ASCII('z')
    BEGIN
        SET @string = REPLACE( @string, ' ' + CHAR(@i), ' ' + CHAR(@i-32))
        SET @i = @i + 1
    END
    SET @string = CHAR(ASCII(LEFT(@string, 1))-32) + RIGHT(@string, LEN(@string)-1)
    RETURN @string
END

我写的procedure
Create procedure splitcolumns()
BEGIN
        SELECT id, Name FROM sometbl
        CROSS APPLY REPLACE(Name, |,id)
END
感觉有点不够

dynatang 发表于 2021-9-14 23:58
我写的procedure
Create procedure splitcolumns()
BEGIN

1. alter procedure splitcolumns
   
2. as
   
3. begin
   
4. Declare @id int
   
5. Declare @NAME varchar(20)
   
6. drop table if exists #temp
   
7. select * into #temp from sometbl where 1=0
   
8. Declare Cur Cursor For Select id,Name From sometbl  
   
9. Open Cur
   
10. Fetch next From Cur Into @id,@Name
    
11. While @@fetch_status=0   
    
12. Begin
    
13.     insert into #temp select @id, value from string_split(@Name,'|')
    
14.     Fetch Next From Cur Into @id,@Name
    
15. End  
    
16. Close Cur  
    
17. Deallocate Cur
    
18. 
    
19. select id, name from #temp
    
20. end   
    

复制代码

dynatang 发表于 2021-9-14 23:57
我查到的function
# in a function
CREATE FUNCTION [dob].initcap(@String varchar(max))

你这段在你的example里是对的，但是如果在一些数字和字符混合的情况下就会出现乱码，比如'32 das fdas fdfsa'，需要判断一下。

谢谢指点， 有什么改进办法吗？还有在哪儿可以测试code？谢谢