HOW TO SEPERATE A NUMERIC VALUE IN A COLUMN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HOW TO SEPERATE A NUMERIC VALUE IN A COLUMN

Author

Topic

akmal
Starting Member

6 Posts

Posted - 2009-07-25 : 10:44:13

Hi Dear Friends,

First look at data which is available in one of the columns of my table
'111001101010111010101'
I want to seperate number (1) and number(0), I mean in another table I will mark 1 as paid and 0 as unpaid.

Regards,
Akmal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-25 : 11:24:29

how do you want to separate it ? Can show us what is the expected output for

'111001101010111010101'

KH
[spoiler]Time is always against us[/spoiler]

akmal
Starting Member

6 Posts

Posted - 2009-07-26 : 02:19:30

Thanks for reply,
Let's take an example:

I have 13 digites for number 1 and 8 digits for number 0, I want to seperate the 13 digit 1 to one column, and the zeros in another column

'111001101010111010101'

column (1) column(0)
1111111111111 00000000

Regards,
Akmal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 02:32:44

[code]
select column1 = replace(col, '0', ''), column0 = replace(col, '1', '')
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-26 : 03:50:17

Also if you want to find no of occurances of 1 & 0 use this

select noof0=len(col)-len(replace(col,'0','')),
noof1=len(col)-len(replace(col,'1',''))
from yourtable

akmal
Starting Member

6 Posts

Posted - 2009-07-26 : 04:00:03

Thanks Alot that helped.

I have one more problem dear friend. Let me explain it.

I have a customer whose a/c is 1001101007812 and I have issued him a check book of 10 pages, I have only the following data.

A/c # =1001101007812
Check starting number =112691
pages =10

I niether have ending nor details of pages like 112691,112692,112693...

My output:

I need to generate in a temp table details of each check number a/c wise:

A/c # Check No
1001102001477 112691
1001102001477 112692
1001102001477 112693
1001102001477 112694
1001102001477 112695
1001102001477 112696
1001102001477 112697
1001102001477 112698
1001102001477 112699
1001102001477 112700

I have thousands of records, your help will really relief my pain. & if it is not disturbing I will personally request your e-mail Id for dired Contact.

Regards,
Akmal

quote:
Originally posted by khtan

select column1 = replace(col, '0', ''), column0 = replace(col, '1', '')
from yourtable

KH
[spoiler]Time is always against us[/spoiler]

akmal
Starting Member

6 Posts

Posted - 2009-07-26 : 04:07:01

OOPS, I mistekenly mentioned the wrong a/c here is the correct one. so that no one should get confused
A/c # Check #
1001101007812 112691
1001101007812 112692
1001101007812 112693
1001101007812 112694
1001101007812 112695
1001101007812 112696
1001101007812 112697
1001101007812 112698
1001101007812 112699
1001101007812 112700

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 04:22:27

make use of F_TABLE_NUMBER_RANGE and CROSS APPLY to it.


from  yourtable t
      cross apply F_TABLE_NUMBER_RANGE(Check_no, Check_no + pages - 1)

KH
[spoiler]Time is always against us[/spoiler]

akmal
Starting Member

6 Posts

Posted - 2009-07-26 : 04:42:13

How to use (F_TABLE_NUMBER_RANGE) I have never used it.
and steps to follow..

Regards,
akmal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 05:21:14

it is user defined function. you can get it from the link that i posted on 07/26/2009 : 04:22:27

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-26 : 05:36:13

Maybe u can use a CTE like this


create table #tbl (ac bigint,chqstartno int,page int)
insert into #tbl
select 1001102001477,112691,10 union all
select 1001102001478,101111,5


;with cte(ac,chqno)
as
(
  select ac,chqstartno from #tbl
union all
select cte.ac,chqno+1 from cte 
inner join #tbl on #tbl.ac=cte.ac
where chqno<=(#tbl.chqstartno +page )-1
)

select * from cte order by ac

	
drop table #tbl

akmal
Starting Member

6 Posts

Posted - 2009-07-26 : 08:14:51

THANKS TO ALL OF YOU GUYS. KEEP IT UP

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-26 : 08:37:58

quote:
Originally posted by akmal

THANKS TO ALL OF YOU GUYS. KEEP IT UP

So what did you use??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources