我在sql server中遇到了这个Query [英] I am stuck with this Query in sql server

问题描述

假设这是我的代理商代码



'DGP 1 01052013'



DGP是字符串



最后8位数是当前日期，整数之间是唯一代码，每个代理商条目应该增加





它应该像



DGP 2 01052013

DGP 3 01052013

...

DGP 10 01052013



...

DGP 100 01052013

代码之间没有空格。



解决方案..？

Assume this is my agent code

'DGP 1 01052013'

DGP is string

the last 8 digit is the current date and the in between integer is unique code ,its should be increase for every agent entry


it should be like

DGP 2 01052013
DGP 3 01052013
...
DGP 10 01052013

...
DGP 100 01052013
no spaces between the code.

Solutions..?

推荐答案

借助Trim功能

查看链接

http://consultingblogs.emc.com/jamespipe/archive/2007/08/14/SQL_3A00_-A-49-character-long-_2700_empty_2700_-field_3F00_ .aspx [ ^ ]



http://blog.sqlauthority.com/2013/01/23/sql-server-trim-function-to-remove-leading-and-trailing-spaces-of-string -sql-in-sixty-seconds-040-video / [ ^ ]

With the help ofTrim function
check the link
http://consultingblogs.emc.com/jamespipe/archive/2007/08/14/SQL_3A00_-A-49-character-long-_2700_empty_2700_-field_3F00_.aspx[^]

http://blog.sqlauthority.com/2013/01/23/sql-server-trim-function-to-remove-leading-and-trailing-spaces-of-string-sql-in-sixty-seconds-040-video/[^]

请参阅此链接：使用SQL Server自定义自动生成的序列 [ ^ ]



问候......

See this link : Custom Auto-Generated Sequences with SQL Server[^]

Regards...

create table  ad
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'DGP'+' '+convert(varchar,ID)+' '+'01052013'
when 2 then 'DGP'+convert(varchar,ID)+'01052013'
else 'DGP'+convert(varchar,ID)
end
)

INSERT INTO ad DEFAULT VALUES

这篇关于我在sql server中遇到了这个Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！