split string in tsql from column

Question

I've got a column in my table with text (string) like 19.5 or 7.14 but as well with just 19 or 11.

I want to split the text in 2 columns 1 with all the text before the "." and 1 with all the text after the "." .

For the entries without a . in the string, the table must have 00 in the second column. Also all data after the . has to be 2 chars long(padded with 0).

e.g. 19.5 must give column1: 19 column2: 50 ; 11 must give column1: 11 column2: 00 Can anybody help me with the correct tsql-code?

TechDo · Accepted Answer · 2013-05-06 09:22:59Z

4

Please try:

select 
    YourCol, 
    SUBSTRING(YourCol, 0, CHARINDEX('.', YourCol+'.')) Col1, 
    LEFT(SUBSTRING(YourCol, CHARINDEX('.', YourCol+'.')+1, 2)+'00', 2) Col2 
from YourTable

or

select
    YourCol,
    CAST(YourCol AS INT) Col1,
    RIGHT(PARSENAME(YourCol,1), 2) Col2
from
(
    select 
        CONVERT(NUMERIC(18,2), YourCol) YourCol
    from YourTable
)x

Sample:

declare @tbl as table(txt nvarchar(10))
insert into @tbl values ('19.5'), ('11'), ('7.14')

select 
    txt, 
    SUBSTRING(txt, 0, CHARINDEX('.', txt+'.')) Col1, 
    LEFT(SUBSTRING(txt, CHARINDEX('.', txt+'.')+1, 2)+'00', 2) Col2 
from @tbl

or

select
    txt,
    CAST(txt AS INT) Col1,
    RIGHT(PARSENAME(txt,1), 2) Col2
from
(
    select 
        CONVERT(NUMERIC(18,2), txt) txt
    from @tbl
)x

edited May 6, 2013 at 9:22

answered May 6, 2013 at 9:02

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

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

Comments

Manikandan Sethuraju · Accepted Answer · 2013-05-06 09:08:41Z

0

Try this,

Declare @InputList VARCHAR(8000), @Delimiter VARCHAR(8000);
Declare @1stvalue nvarchar(20), @2ndValue Int;

Set @InputList = '19.5';
Set @Delimiter = '.';

set @1stvalue = (select RTRIM(LTRIM(SUBSTRING(@InputList,1,CHARINDEX(@Delimiter,@InputList,0)-1))));

set @2ndValue = (Select RTRIM(LTRIM(SUBSTRING(@InputList,CHARINDEX(@Delimiter,@InputList,0)+LEN(@Delimiter),LEN(@InputList)))) )
set @2ndValue = (select case when @2ndValue < 10 then convert (varchar(10) ,convert (varchar(10),@2ndValue) + '0') else convert (varchar(10), @2ndValue) end);

SELECT @1stvalue AS Column1, @2ndValue AS Column2;

You can use this as function..

answered May 6, 2013 at 9:08

Manikandan Sethuraju

2,90110 gold badges32 silver badges48 bronze badges

Comments

t-clausen.dk · Accepted Answer · 2013-05-06 09:30:52Z

0

select cast(cast(number as float) as int) column1,
right(cast(number as numeric(9,2)), 2) column2
from
(VALUES ('19.5'),('7.14'),('19'),('11') ) t(number)

edited May 6, 2013 at 9:30

answered May 6, 2013 at 9:04

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Collectives™ on Stack Overflow

split string in tsql from column

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related