To separate STRING and INTEGER in SQL

Question

I have a column which contains both string and integer( Eg: 125D , S234,.. ) Now I want to separate the integer and the string. How to do this in SQL server???

is this a one time operation, or will you need to repeat this as a data cleansing operation? — Stuart Ainsworth
– Stuart Ainsworth, Commented Mar 4, 2014 at 17:20

Andrew · Accepted Answer · 2014-03-04 17:55:37Z

1

This should get you what you need. If it's more than a one time thing, you could make this into a function. Or maybe you could split it a load time, if you have some sort of ETL process.

DECLARE @MyString varchar(1000) = 'A1b2C3';

declare @Counter integer = 1;
declare @currChar char(1) = '';;
declare @charString varchar(1000) = '';
declare @intString varchar(1000) = '';



while (@Counter <= len(@MyString))
begin
 set @currChar = substring(@MyString,@Counter,1)
   if isNumeric(@currChar) = 1
          set @intString = @intString + @currChar
   else
       set @charString = @charString + @currChar

  set @counter = @counter + 1
end

select @charString;
select cast(@intString as integer)

edited Mar 4, 2014 at 17:55

answered Mar 4, 2014 at 17:42

Andrew

8,8933 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

To separate STRING and INTEGER in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related