SQL REPLACE 的奇怪输出

发布于 2024-07-05 14:37:58 字数 720 浏览 6 评论 0原文

我在 SQL 视图中使用 REPLACE 来删除属性编号中的空格。该函数的设置如下 REPLACE(pin, ' ', '')。在绿屏上，查询看起来不错。在其他任何事情中，我们都会获取字段中字符的十六进制值。我确信这是一个编码问题，但是我该如何解决它呢？

这是我用来创建视图的语句：

CREATE VIEW RLIC2GIS AS SELECT REPLACE(RCAPIN, ' ', '') AS 
RCAPIN13 , RLICNO, RONAME, ROADR1, ROADR2, ROCITY, ROSTAT, ROZIP1, 
ROZIP2, RGRID, RRADR1, RRADR2, RANAME, RAADR1, RAADR2, RACITY,     
RASTAT, RAZIP1, RAZIP2, REGRES, RPENDI, RBLDGT, ROWNOC, RRCODE,    
RROOMS, RUNITS, RTUNIT, RPAID, RAMTPD, RMDYPD, RRFUSE, RNUMCP,     
RDATCP, RINSP, RCAUKY, RCAPIN, RAMTYR, RYREXP, RDELET, RVARIA,     
RMDYIN, RDTLKI, ROPHN1, ROPHN2, ROCOM1, ROCOM2, RAPHN1, RAPHN2,    
RACOM1, RACOM2, RNOTES FROM RLIC2

更新：我在下面发布了答案。

原文

I am using REPLACE in an SQL view to remove the spaces from a property number. The function is setup like this REPLACE(pin, ' ', ''). On the green-screen the query looked fine. In anything else we get the hex values of the characters in the field. I am sure it is an encoding thing, but how do I fix it?

Here is the statement I used to create the view:

CREATE VIEW RLIC2GIS AS SELECT REPLACE(RCAPIN, ' ', '') AS 
RCAPIN13 , RLICNO, RONAME, ROADR1, ROADR2, ROCITY, ROSTAT, ROZIP1, 
ROZIP2, RGRID, RRADR1, RRADR2, RANAME, RAADR1, RAADR2, RACITY,     
RASTAT, RAZIP1, RAZIP2, REGRES, RPENDI, RBLDGT, ROWNOC, RRCODE,    
RROOMS, RUNITS, RTUNIT, RPAID, RAMTPD, RMDYPD, RRFUSE, RNUMCP,     
RDATCP, RINSP, RCAUKY, RCAPIN, RAMTYR, RYREXP, RDELET, RVARIA,     
RMDYIN, RDTLKI, ROPHN1, ROPHN2, ROCOM1, ROCOM2, RAPHN1, RAPHN2,    
RACOM1, RACOM2, RNOTES FROM RLIC2

UPDATE: I posted the answer below.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

老旧海报 2024-07-12 14:37:58

尝试使用 NULL 而不是空字符串。即 REPLACE(RCAPIN, ' ', NULL)

回复收藏 0 原文

榕城若虚 2024-07-12 14:37:58

这里的问题可能是您认为该字段中的空白字符实际上是其他一些不可打印的字符。

您可以使用以下 SQL 来查看第 4 个位置处的 ASCII 字符：

SELECT ascii(substr(RCAPIN,4,1)) 
FROM YOUR-TABLE

然后您可以使用该字符的替换来代替空格：

SELECT replace(RCAPIN,chr(9))
FROM YOUR-TABLE

The problem here might be that what you think is the blank character in that field is actually some other unprintable character.

You can use the following SQL to see what ASCII character is at the 4th position:

SELECT ascii(substr(RCAPIN,4,1)) 
FROM YOUR-TABLE

Then you would be able to use a replace for that character instead of the blank space:

SELECT replace(RCAPIN,chr(9))
FROM YOUR-TABLE

回复收藏 0 原文

玩世 2024-07-12 14:37:58

我们最终使用 concat 和 substring 来获得我们想要的结果。

CREATE VIEW RLIC2GIS AS                                  
SELECT CONCAT(SUBSTR(RCAPIN,1,3),CONCAT(SUBSTR(RCAPIN,5,2),      
CONCAT(SUBSTR(RCAPIN,8,2), CONCAT(SUBSTR(RCAPIN,11,3),           
SUBSTR(RCAPIN, 15,3))))) AS CAPIN13, RLICNO, RONAME, ROADR1,     
ROADR2, ROCITY, ROSTAT, ROZIP1, ROZIP2, RGRID, RRADR1, RRADR2,   
RANAME, RAADR1, RAADR2, RACITY, RASTAT, RAZIP1, RAZIP2, REGRES,  
RPENDI, RBLDGT, ROWNOC, RRCODE, RROOMS, RUNITS, RTUNIT, RPAID,   
RAMTPD, RMDYPD, RRFUSE, RNUMCP, RDATCP, RINSP, RCAUKY, RCAPIN,   
RAMTYR, RYREXP, RDELET, RVARIA, RMDYIN, RDTLKI, ROPHN1, ROPHN2,  
ROCOM1, ROCOM2, RAPHN1, RAPHN2, RACOM1, RACOM2, RNOTES FROM RLIC2

We ended up using concat and substring to get the results we wanted.

CREATE VIEW RLIC2GIS AS                                  
SELECT CONCAT(SUBSTR(RCAPIN,1,3),CONCAT(SUBSTR(RCAPIN,5,2),      
CONCAT(SUBSTR(RCAPIN,8,2), CONCAT(SUBSTR(RCAPIN,11,3),           
SUBSTR(RCAPIN, 15,3))))) AS CAPIN13, RLICNO, RONAME, ROADR1,     
ROADR2, ROCITY, ROSTAT, ROZIP1, ROZIP2, RGRID, RRADR1, RRADR2,   
RANAME, RAADR1, RAADR2, RACITY, RASTAT, RAZIP1, RAZIP2, REGRES,  
RPENDI, RBLDGT, ROWNOC, RRCODE, RROOMS, RUNITS, RTUNIT, RPAID,   
RAMTPD, RMDYPD, RRFUSE, RNUMCP, RDATCP, RINSP, RCAUKY, RCAPIN,   
RAMTYR, RYREXP, RDELET, RVARIA, RMDYIN, RDTLKI, ROPHN1, ROPHN2,  
ROCOM1, ROCOM2, RAPHN1, RAPHN2, RACOM1, RACOM2, RNOTES FROM RLIC2

回复收藏 0 原文

~没有更多了~