blocks|key|3966236|text|一个简单的select查询将只返回表上的内容，而不管它是否加密。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3966237|所以，我认为你不能在数据库级别上这样做。|3966238|根据您的要求，您需要在应用程序中使用双向加密算法，因此您可以在将数据加密保存在数据库中之前对其进行加密，然后从数据库中获取加密信息并在应用程序中对其进行解密。|3966239|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

A simple select query would return just what it's on the table, no matter if it's encrypted or not.

So, I think you can not do this on the database level.

For your requirement, you would need a bidirectional encryption algorithm to use in your application, so you can encrypt data before saving it encrypted on the database, and get the encrypted information from the database and decrypt it on your applicaition.

blocks|key|3966292|text|您必须使用视图，并拒绝所有用户对基础表的SELECT访问。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3966293|您的视图将如下所示|3966294|SELECT+
+++++SUBSTRING(x.SecurityNumber,1,3)+%2B+
+++++'xxxxx'+%2B+
+++++SUBSTRING(x.SecurityNumber,LEN(x.SecurityNumber)+-+2,+LEN(x.SecurityNumber))
+++++AS+column1
FROM+underlyingTable+x|code-block|syntax|javascript|3966295|然后，您可以授予用户对此视图的SELECT访问权限，并以您所描述的方式屏蔽输出。|3966296|如果希望客户端软件能够在该表中插入或更新数据，则可以使用INSTEAD+OF+insert或INSTEAD+OF+update触发器来更新基表。|3966297|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You would have to use a view, and deny all users SELECT access to the underlying table. 

Your view would look something like

<pre><code>SELECT 
 SUBSTRING(x.SecurityNumber,1,3) + 
 'xxxxx' + 
 SUBSTRING(x.SecurityNumber,LEN(x.SecurityNumber) - 2, LEN(x.SecurityNumber))
 AS column1
FROM underlyingTable x
</code></pre>

You could then grant your users SELECT access to just this view and have the out masked in the way you described.

If you wanted your client software to be able to insert or update data in this table, you would use an INSTEAD OF INSERT or INSTEAD OF UPDATE trigger to update the base table.

blocks|key|72377|text|如果您知道您的数据字段将有多长，那么您可以使用另一个答案将生成的静态版本，但您始终可以创建一个函数来生成以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|72378|CREATE+FUNCTION+MixUpCharacters
(
++++@OrigVal+varchar(max)
)
RETURNS+varchar(max)
AS
BEGIN

DECLARE+@NewVal+varchar(max)
DECLARE+@OrigLen+int
DECLARE+@LoopCt+int
DECLARE+@Part+varchar(max)+=+''
Declare+@PartLength+int

SET+@NewVal+=+''
SET+@OrigLen+=+DATALENGTH(@OrigVal)
SET+@LoopCt+=+1

SET+@Part+=+SUBSTRING(@OrigVal,+4,+len(@OrigVal)-6)
set+@PartLength+=+LEN(@Part)

WHILE+@LoopCt+<=+@PartLength
++++BEGIN
++++++++--+Current+length+of+possible+characters
++++++++SET+@NewVal+=+@NewVal+%2B+'X'

++++++++--+Advance+the+loop
++++++++SET+@LoopCt+=+@LoopCt+%2B+1
++++END

++++Return+REPLACE(@OrigVal,+@Part,+@NewVal)
END|code-block|syntax|javascript|72379|对于此函数，您将传入要屏蔽的值。因此，您的查询将是：|72380|declare+@temp+table
(
++++col1+varchar(50)
)

insert+into+@temp
values+('384844033434'),+('743423547878'),+('111224678885')

select+dbo.MixUpCharacters(col1)+col1
from+@temp|72381|请参阅SQL+Fiddle+with+Demo|offset|length|72382|结果将是：|72383|%7C+++++++++COL1+%7C
----------------
%7C+384XXXXXX434+%7C
%7C+743XXXXXX878+%7C
%7C+111XXXXXX885+%7C|72384|或者这里有一种使用递归CTE的方法：|72385|;with+data(col1)+as
(
++++select+'384844033434'
++++union+all
++++select+'7434235878'
++++union+all
++++select+'111224678885'
),
s1+(col1,++repfull)+as
(
++++select+col1,+
++++++++SUBSTRING(col1,+4,+len(col1)-6)+repfull
++++from+data
),
s2+(col1,+item,+repfull,+r)+as
(
++++select+col1,
++++++++cast('x'+as+varchar(max)),
++++++++right(repfull,+LEN(repfull)-1),
++++++++repfull
++++from+s1
++++union+all
++++select+col1,++
++++++++'x'%2B+cast(item+as+varchar(max)),
++++++++right(repfull,+LEN(repfull)-1),
++++++++r
++++from+s2
++++where+len(repfull)+>0
)
select+REPLACE(col1,+r,+item)+newValue
from
(
++++select+col1,+item,+R,
++++++++ROW_NUMBER()+over(partition+by+col1+order+by+len(item)+desc)+rn
++++from+s2
)+src
where+rn+=+1|72386|请参阅SQL+Fiddle+with+Demo|72387|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!3/a197c/3|1|http://sqlfiddle.com/#!3/d41d8/7061^0|0|0|0|0|3|K|0|0|0|0|0|0|3|K|1|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1A|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1C|8|@]|9|@$M|1D|N|1E|1|1F]]|A|$]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1K|8|@]|9|@$M|1L|N|1M|1|1N]]|A|$]]|$1|Y|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]|16|$5|11|12|13|A|$14|17]]]]

If you know how long your field of data will be, then you can use the static version that the other answer will produce, but you can always create a function to generate this:

<pre><code>CREATE FUNCTION MixUpCharacters
(
 @OrigVal varchar(max)
)
RETURNS varchar(max)
AS
BEGIN

DECLARE @NewVal varchar(max)
DECLARE @OrigLen int
DECLARE @LoopCt int
DECLARE @Part varchar(max) = ''
Declare @PartLength int

SET @NewVal = ''
SET @OrigLen = DATALENGTH(@OrigVal)
SET @LoopCt = 1

SET @Part = SUBSTRING(@OrigVal, 4, len(@OrigVal)-6)
set @PartLength = LEN(@Part)

WHILE @LoopCt &lt;= @PartLength
 BEGIN
 -- Current length of possible characters
 SET @NewVal = @NewVal + 'X'

 -- Advance the loop
 SET @LoopCt = @LoopCt + 1
 END

 Return REPLACE(@OrigVal, @Part, @NewVal)
END
</code></pre>

For this function you will pass in the values that you want to mask. So your query would be:

<pre><code>declare @temp table
(
 col1 varchar(50)
)

insert into @temp
values ('384844033434'), ('743423547878'), ('111224678885')

select dbo.MixUpCharacters(col1) col1
from @temp
</code></pre>

See <a href="http://sqlfiddle.com/#!3/a197c/3" rel="nofollow">SQL Fiddle with Demo</a>

The result would be:

<pre><code>| COL1 |
----------------
| 384XXXXXX434 |
| 743XXXXXX878 |
| 111XXXXXX885 |
</code></pre>

Or here is a way to do it with recursive CTE:

<pre><code>;with data(col1) as
(
 select '384844033434'
 union all
 select '7434235878'
 union all
 select '111224678885'
),
s1 (col1, repfull) as
(
 select col1, 
 SUBSTRING(col1, 4, len(col1)-6) repfull
 from data
),
s2 (col1, item, repfull, r) as
(
 select col1,
 cast('x' as varchar(max)),
 right(repfull, LEN(repfull)-1),
 repfull
 from s1
 union all
 select col1, 
 'x'+ cast(item as varchar(max)),
 right(repfull, LEN(repfull)-1),
 r
 from s2
 where len(repfull) &gt;0
)
select REPLACE(col1, r, item) newValue
from
(
 select col1, item, R,
 ROW_NUMBER() over(partition by col1 order by len(item) desc) rn
 from s2
) src
where rn = 1
</code></pre>

See <a href="http://sqlfiddle.com/#!3/d41d8/7061" rel="nofollow">SQL Fiddle with Demo</a>

blocks|key|3966362|text|非常简单的兄弟|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3966363|SELECT+CONCAT+(SUBSTR('Your+string',1,3),LPAD(SUBSTR('Your+string',-3),LENGTH('Your+string')-1,'*'))+RESULT+FROM+dual|code-block|syntax|javascript|3966364|输出：|3966365|3966366|你*|blockquote|3966367|3966368|3966369|如果是num类型，则转换为char|offset|length|style|CODE|3966370|entityMap^0|0|0|0|0|0|0|0|3|3|D|4|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|13|8|@$Q|14|R|15|S|T]|$Q|16|R|17|S|T]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|V|$]]

very simple bro

<pre><code>SELECT CONCAT (SUBSTR('Your string',1,3),LPAD(SUBSTR('Your string',-3),LENGTH('Your string')-1,'*')) RESULT FROM dual
</code></pre>

output :

<blockquote>
 You*******ing
</blockquote>

if it is <code>num</code> type convert to <code>char</code>

blocks|key|72407|text|如果你想屏蔽你不知道字段长度的字段。您可以像这样升级bluefeet的代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|72408|ALTER+FUNCTION+MixUpCharacters
(
++++@OrigVal+varchar(MAX)
)
RETURNS+varchar(MAX)
AS
BEGIN

DECLARE+@NewVal+NVARCHAR(MAX)
DECLARE+@OrigLen+INT
DECLARE+@LoopCt+INT
DECLARE+@Part+NVARCHAR(MAX)+=+''
DECLARE+@PartLength+INT+++++--+MastLength
DECLARE+@PartStartIndex+INT+--+MaskStartIndex

SET+@NewVal+=+''
SET+@LoopCt+=+1
SET+@OrigLen+=+LEN(@OrigVal)

IF(@OrigLen+=+1)
++++BEGIN
++++++++RETURN+'X'
++++END
IF(@OrigLen+<+6)
++++BEGIN
++++++++SET+@PartStartIndex+=+@OrigLen+/+2
++++++++SET+@PartLength+=+@OrigLen+-+@PartStartIndex
++++++++SET+@Part+=+SUBSTRING(@OrigVal,+@PartStartIndex,++@PartLength)
++++END
ELSE+IF(@OrigLen+<+8)
++++BEGIN
++++++++SET+@PartStartIndex+=+3
++++++++SET+@PartLength+=+@OrigLen+-+@PartStartIndex+-+1
++++++++SET+@Part+=+SUBSTRING(@OrigVal,+@PartStartIndex,++@PartLength)
++++END
ELSE
++++BEGIN
++++++++SET+@PartStartIndex+=+4
++++++++SET+@PartLength+=+@OrigLen+-+@PartStartIndex+-+2
++++++++SET+@Part+=+SUBSTRING(@OrigVal,+@PartStartIndex,++@PartLength)
++++END

WHILE+@LoopCt+<=+@PartLength
++++BEGIN
++++++++--+Current+length+of+possible+characters
++++++++SET+@NewVal+=+@NewVal+%2B+'X'

++++++++--+Advance+the+loop
++++++++SET+@LoopCt+=+@LoopCt+%2B+1
++++END

++++RETURN+REPLACE(@OrigVal,+@Part,+@NewVal)
END|code-block|syntax|javascript|72409|您可以像这样测试：|72410|SELECT+dbo.MixUpCharacters('1')
UNION+ALL
SELECT+dbo.MixUpCharacters('12')
UNION+ALL
SELECT+dbo.MixUpCharacters('123')
UNION+ALL
SELECT+dbo.MixUpCharacters('1234')
UNION+ALL
SELECT+dbo.MixUpCharacters('12345')
UNION+ALL
SELECT+dbo.MixUpCharacters('123456')
UNION+ALL
SELECT+dbo.MixUpCharacters('1234567')
UNION+ALL
SELECT+dbo.MixUpCharacters('12345678')
UNION+ALL
SELECT+dbo.MixUpCharacters('123456789')
UNION+ALL
select+dbo.MixUpCharacters('1234567890')
UNION+ALL
select+dbo.MixUpCharacters('12345678910')|72411|结果：|72412|X
X2
XX3
1XX4
1XXX5
12XX56
12XXX67
123XX678
123XXX789
123XXXX890
123XXXXX910|72413|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

If you want to mask field which you dont know about field's length. You can upgrade bluefeet's code like this:

<pre><code>ALTER FUNCTION MixUpCharacters
(
 @OrigVal varchar(MAX)
)
RETURNS varchar(MAX)
AS
BEGIN

DECLARE @NewVal NVARCHAR(MAX)
DECLARE @OrigLen INT
DECLARE @LoopCt INT
DECLARE @Part NVARCHAR(MAX) = ''
DECLARE @PartLength INT -- MastLength
DECLARE @PartStartIndex INT -- MaskStartIndex

SET @NewVal = ''
SET @LoopCt = 1
SET @OrigLen = LEN(@OrigVal)

IF(@OrigLen = 1)
 BEGIN
 RETURN 'X'
 END
IF(@OrigLen &lt; 6)
 BEGIN
 SET @PartStartIndex = @OrigLen / 2
 SET @PartLength = @OrigLen - @PartStartIndex
 SET @Part = SUBSTRING(@OrigVal, @PartStartIndex, @PartLength)
 END
ELSE IF(@OrigLen &lt; 8)
 BEGIN
 SET @PartStartIndex = 3
 SET @PartLength = @OrigLen - @PartStartIndex - 1
 SET @Part = SUBSTRING(@OrigVal, @PartStartIndex, @PartLength)
 END
ELSE
 BEGIN
 SET @PartStartIndex = 4
 SET @PartLength = @OrigLen - @PartStartIndex - 2
 SET @Part = SUBSTRING(@OrigVal, @PartStartIndex, @PartLength)
 END

WHILE @LoopCt &lt;= @PartLength
 BEGIN
 -- Current length of possible characters
 SET @NewVal = @NewVal + 'X'

 -- Advance the loop
 SET @LoopCt = @LoopCt + 1
 END

 RETURN REPLACE(@OrigVal, @Part, @NewVal)
END
</code></pre>

You can test like this:

<pre><code>SELECT dbo.MixUpCharacters('1')
UNION ALL
SELECT dbo.MixUpCharacters('12')
UNION ALL
SELECT dbo.MixUpCharacters('123')
UNION ALL
SELECT dbo.MixUpCharacters('1234')
UNION ALL
SELECT dbo.MixUpCharacters('12345')
UNION ALL
SELECT dbo.MixUpCharacters('123456')
UNION ALL
SELECT dbo.MixUpCharacters('1234567')
UNION ALL
SELECT dbo.MixUpCharacters('12345678')
UNION ALL
SELECT dbo.MixUpCharacters('123456789')
UNION ALL
select dbo.MixUpCharacters('1234567890')
UNION ALL
select dbo.MixUpCharacters('12345678910')
</code></pre>

RESULTS:

<pre><code>X
X2
XX3
1XX4
1XXX5
12XX56
12XXX67
123XX678
123XXX789
123XXXX890
123XXXXX910
</code></pre>

blocks|key|3966430|text|这也适用于检索时的屏蔽输出。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3966431|SELECT+CONCAT(SUBSTRING(column1,+1,+3),'xxxxxx',SUBSTRING(column1,+10,+3))+FROM+tableName;|code-block|syntax|javascript|3966432|如果值长度不是固定的，可以使用下面的。|3966433|SELECT+CONCAT(SUBSTRING(column1,+1,+3),'xxxxxx',SUBSTRING(column1,+LEN(column1)-2,+3))+FROM+tableName;|3966434|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This also works for masking output when retrieve.
<pre><code>SELECT CONCAT(SUBSTRING(column1, 1, 3),'xxxxxx',SUBSTRING(column1, 10, 3)) FROM tableName;
</code></pre>
If the value length is not fixed, can use the below.
<pre><code>SELECT CONCAT(SUBSTRING(column1, 1, 3),'xxxxxx',SUBSTRING(column1, LEN(column1)-2, 3)) FROM tableName;
</code></pre>

blocks|key|3966456|text|concat('XXXXXXXXX',right(aadharno,4))+as+aadharno|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3966457|它将掩蔽aadhar数字，右数第四个数字。|unstyled|3966458|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>concat('XXXXXXXXX',right(aadharno,4)) as aadharno
</code></pre>
It will mask the aadhar number, fourth from the right.

I'm using SQL Server 2008.

I would like to character mask the output data of a query.

This is my data from a column on a table when doing a select:

<pre><code>column1

384844033434

743423547878

111224678885
</code></pre>

I would like an output like this:

<pre><code>column1

384xxxxxx434

743xxxxxx878

111xxxxxx885
</code></pre>

How can I do this?

Character mask output data on select

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我使用的是SQL Server 2008。我想对查询的输出数据进行字符掩码。这是我在执行select操作时从表中的列中获得的数据：column1384844033434743423547878111224678885我想要这样的输出：column1384xxxxxx434743xxxxxx878111xxxxxx885...

问select时字符掩码输出数据
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问select时字符掩码输出数据EN