如何大写和小写一个字符串 [英] How to upper case and lower case a string

问题描述

'IBM LONDON AND MANCHESTER HP'



我有一张包含上述数据的表格。 IBM始终处于起步阶段，HP始终处于起步阶段。



现在我需要将此varchar更改为以下内容 -



'IBM London and曼彻斯特惠普'



所以IBM，HP应该总是上限，

AND小写和

和所有其他单词，第一个字母表Capital。



有什么建议我如何使用My Sql实现这一点？

推荐答案

这是一个典型的编程任务。虽然您可以编写一个存储过程来将列出的规则应用于输入字符串，但我不会这样编码（我会使用'真正的'编程语言）。

That's a typical programming task. Though you may write a stored procedure to apply the rules you listed to the input string, I wouldn't code it that way (I would use 'real' programming language).

我全心全意地同意CPallini在SQL之外执行此操作，但如果您没有选择，那么这是我过去使用的一种技术。警告！这很快就会变得无法实现！



创建一个包含您想要更改的内容的查找表...例如

I wholeheartedly agree with CPallini on doing this outside of SQL, but if you have no option then this is a technique I've used in the past. Warning! This can very rapidly become non-performant!

Create a look up table that will contain things you want to change ... e.g.

CREATE TABLE LookupX
(
  Source varchar(50),
  ReplaceWith varchar(50)
);

（你可能想要在这个上创建一个索引（Source），不允许空值等）



用你已经知道的东西填充它...例如

(You might want create an index on this (Source), not allow nulls etc)

Populate it with those things you already know about ...e.g.

INSERT INTO LookupX (Source, ReplaceWith) VALUES('ibm','IBM');
INSERT INTO LookupX (Source, ReplaceWith)  VALUES('london','London');
INSERT INTO LookupX (Source, ReplaceWith)  VALUES('manchester ', 'Manchester');
INSERT INTO LookupX (Source, ReplaceWith)  VALUES('hp','HP');

请注意， Source 总是小写 - 所有这些（希望）会在一瞬间变得清晰。



我还创建了一个测试表，我将在此处转储以获得完整性

Note that Source is always in lower case - all will (hopefully) become clear in a moment.

I also created a test table that I will dump here for completeness

CREATE TABLE Test
(
  Col1 varchar(250)
  );
INSERT INTO Test (Col1) VALUES ('IBM LONDON AND MANCHESTER HP');
INSERT INTO Test (Col1) VALUES ('another thing hp london');

然后我创建一个包含我要调整的列的临时表

I then create a temporary table containing the column I want to adjust

SELECT LOWER(Col1) AS Col1 INTO #TT FROM Test;

再次注意 LOWER 。我正在预先做这个，因为我可能希望通过这个表格进行多次传递。



然后构建一个光标，逐步遍历我的Lookup表并尝试替换具有适当大小写的文本

Note again the LOWER. I'm doing this up front as I might want to make many passes through this table.

I then build a cursor that steps through my Lookup table and attempts to replace the text with the appropriate capitalisation

DECLARE @SOURCE VARCHAR(250)
DECLARE @REP VARCHAR(250)
DECLARE X CURSOR FOR
  SELECT Source, ReplaceWith
  FROM LookupX

  OPEN X
  FETCH NEXT FROM X INTO @SOURCE, @REP

  WHILE @@FETCH_STATUS = 0
  BEGIN
    UPDATE #TT SET Col1 = REPLACE(Col1, @SOURCE, @REP)
    FETCH NEXT FROM X INTO @SOURCE, @REP
  END
    CLOSE X
  DEALLOCATE X

#TT现在应该包含你想要的数据 - 你可以直接做更换回原始表格，或者在此之后使用#TT，如果你没有把它放入存储过程中。



只是为了确认从我的 SELECT * FROM #TT 输出

IBM London and Manchester HP 
another thing HP London 

如果沿着这条路走下去，你可能不得不想办法只替换整个单词 - PATINDEX和正则表达式。



丑陋，但是它确实有效

If you go down this route you're probably going to have to work out a way of only replacing whole words - PATINDEX and a regular expression perhaps.

Ugly, but it does work

这篇关于如何大写和小写一个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！