| Author |
 Topic  |
|
|
skativarapu
Starting Member
13 Posts |
 Posted - 06/23/2006 : 10:35:26
|
Hi all, iam trying to find a string function which would replace column value where there is a ssn with 1 and anything else(blank,null,...) with 0. i need to count the number of rows with ssn and one without ssn.
i checked few string functions but no use
any help appreciated |
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 06/23/2006 : 10:39:18
|
1 and anything else? Wouldn't that be all of them? 1, blank, null, and space would be sufficient, or are you actually wanting an algorithm that finds out if it's a valid SSN or not?
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
 |
|
|
skativarapu
Starting Member
13 Posts |
Posted - 06/23/2006 : 10:46:06
|
all iam trying to do is to create a new column which will have 1 if there is a ssn in the B_SSN column and if there is null or blank B_SSN colum then the new column will will have 0
so that i can count the number of loan enquiries with ssn and one without ssn |
|
|
|
skativarapu
Starting Member
13 Posts |
Posted - 06/23/2006 : 11:05:58
|
| to be more clear iam writing a select statement which will insert data from my sorce table. in my sorce table i have a column B_SSN which either has a ssn or its null/blank. iam trying to find a function which will give an output 1 if there is a ssn in that column zero if null or blank |
|
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 06/23/2006 : 12:01:05
|
Like this?
--data
declare @t table (id int identity(1, 1), B_SSN varchar(10))
insert @t
select null
union all select 'abc'
union all select ''
union all select ' '
union all select 'xyz'
--calculation
select *, case when len(isnull(B_SSN, '')) = 0 then 0 else 1 end as 'Non-null/blank' from @t
/*
id B_SSN Non-null/blank
----------- ---------- --------------
1 NULL 0
2 abc 1
3 0
4 0
5 xyz 1
*/
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
skativarapu
Starting Member
13 Posts |
Posted - 06/23/2006 : 12:16:31
|
mighrt not be big butit may help anyone
the query below displays id,ssn and 1 if there is a ssn in the B_SSN column and if there is null or blank B_SSN colum then the new column will have 0
select id,b1_ssn,case when b1_ssn is null then 0
when b1_ssn = ' ' then 0 else 1 END AS ssn_filled from leadloan |
|
|
| |
Topic |
|
string function
