如何在sql中将1列提取为2列 [英] How Extract 1 Column as 2 columns in sql

问题描述

嗨如何在sql中将1列提取为2列



例如：GP100分为GP和100

GPS20是否为进入GPS和20







请帮帮我

解决方案

创建以下功能

 创建 功能 [dbo]。[RemoveNonAlphaCharacters]（ @ Temp   VarChar （ 1000 ））
 返回  VarChar （ 1000 ）
  AS  
 开始 
 
 声明  @ KeepValues   as   varchar （ 50 ） 
  set  @ KeepValues = ' ％[^ az]％' 
   PatIndex（ @ KeepValues ， @ Temp ）>  0  
 设置  @ Temp  = Stuff（ @ Temp ，PatIndex（ @ KeepValues ， @ Temp ）， 1 ，' '）
 
 返回  @ Temp  
 结束 

现在运行此查询

     
（
  select  '  GP100'  as  no1 
  union   all  
  select  '  GPS120' 
）
 选择 no1，dbo.RemoveNonAlphaCharacters（no1）字母表，替换（no1，dbo.RemoveNonAlphaCharacters（no1），' '）nos 来自 a 

快乐编码！

：）

使用子串函数....

请查看以下链接...



http://msdn.microsoft.com/en-IN/library/ ms187748.aspx [ ^ ]





http://www.sql-server-helper.com/tips/tip -of最day.aspx？TKEY = 4AB0642 1-E859-4B5F-A948-0C9640F3108D& tkw = uses-of-substring-string-function [ ^

hi How Extract 1 Column as 2 columns in sql

for ex: GP100 is devide into GP and 100
GPS20 is devide into GPS and 20



please help me

解决方案

Create below function

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
 set @KeepValues= '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

now run this query

with a as
(
select 'GP100' as no1
union all
select 'GPS120'
)
Select no1, dbo.RemoveNonAlphaCharacters(no1) alphabets,replace(no1,dbo.RemoveNonAlphaCharacters(no1),'') nos from a

Happy Coding!
:)

hi,

Use Substring function ....
Please look into the following link...

http://msdn.microsoft.com/en-IN/library/ms187748.aspx[^]


http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=4AB06421-E859-4B5F-A948-0C9640F3108D&tkw=uses-of-the-substring-string-function[^]

这篇关于如何在sql中将1列提取为2列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！