如何在sql server 2008中使用lpad功能 [英] how to use lpad function work in sql server 2008

问题描述

我在asp页面中有一个标签...我想在该标签中打印autogenret 10位数字..



我的sql查询是

  USE  [Test_Inventory] ​​
  GO  
  / *   *****对象：StoredProcedure [dbo]。[SPSelectDCNMForMaxRecord]脚本日期：07/01 / 2013 16:50:14 ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
  ALTER   PROCEDURE  [dbo]。[SPSelectDCNMForMaxRecord] 
 < span class =code-keyword> AS  
  BEGIN  
   -   添加SET NOCOUNT ON以防止额外的结果集 
   -   干扰SELECT语句。 
  SET  < span class =code-keyword> NOCOUNT   ON ; 
 
   -   在此处插入程序语句 
 选择 isnull（MAX（DC_NO）， 0000000000 ） as  MAXRecord 从 DCNM 
 
 
 
  END  

解决方案

  SELECT   RIGHT （'  0000000000' +  CONVERT （ VARCHAR ，DC_NO）， 10 ）
  FROM  DCNM 

TSQL

 USE [cpqaAnswers ] 
 GO 
 CREATE TABLE [cpqaAnswers]。[cpqa]。[tbl_HS_lpadLabel]（
 [Idx] [int] IDENTITY（1,1），
 [label] [nvarchar （ 341）
）

 DECLARE @intMax [int] 
 SET @intMax = 2214323 
 DECLARE @intCount [int] 
 SET @intCount = 1 

 WHILE @intCount< @intMax + 1 
 BEGIN 
 INSERT INTO [cpqaAnswers]。[cpqa]。[tbl_HS_lpadLabel]（[Label]）
 SELECT CAST（left（replicate（'0'，10），10 -LEN（@intCount））AS [nvarchar]（18））+ CAST（@intCount AS [nvarchar]（18））
 SET @intCount = @intCount + 1 
 END 

i have one label in asp page ...i want to print autogenret 10 digit number in that label..

my sql query is

USE [Test_Inventory]
GO
/****** Object:  StoredProcedure [dbo].[SPSelectDCNMForMaxRecord]    Script Date: 07/01/2013 16:50:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPSelectDCNMForMaxRecord]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    Select isnull(MAX(DC_NO),0000000000) as MAXRecord From DCNM



END

解决方案

SELECT RIGHT('0000000000' + CONVERT(VARCHAR,DC_NO), 10)
FROM DCNM

TSQL

USE [cpqaAnswers]
GO
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_HS_lpadLabel](
	[Idx][int]IDENTITY(1,1),
		[label][nvarchar](341)
		)

DECLARE @intMax [int]
SET @intMax = 2214323
DECLARE @intCount [int]
SET @intCount = 1

WHILE @intCount < @intMax + 1
BEGIN
 INSERT INTO [cpqaAnswers].[cpqa].[tbl_HS_lpadLabel] ([Label])
  SELECT CAST(left(replicate('0', 10), 10-LEN(@intCount)) AS [nvarchar](18)) + CAST(@intCount AS [nvarchar](18))
 SET @intCount = @intCount + 1
END

这篇关于如何在sql server 2008中使用lpad功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！