| Author |
 Topic  |
|
|
vasu4us
Posting Yak Master
102 Posts |
 Posted - 09/13/2006 : 12:33:24
|
iam trying to write a string function which will give me the id part of a mail id
but iam geting the string along with @ and when iam trying to remove the last char (@) iam getting error
query:
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail)))
from lead_details -----> Gives me id along with @
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1))
from lead_details ------------>gives me error "Invalid length parameter passed to the substring function."
But
select (CHARINDEX('@', leadassignedtombemail) - 1) from lead_details
works and gives me the length of id without counting @
where did i go wrong
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 09/13/2006 : 12:48:39
|
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1))
from lead_details
where leadassignedtombemail like '%@%'
or try
where leadassignedtombemail like '_%@%'
you probbaly have some without an @ sign so charindex gives 0.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 09/13/2006 : 15:24:44
|
Hay thanks man i just found the same and resolved the problem
the problem was not in the query but in the data, which i was not looking at and was beating around the query.
Thanks |
|
|
| |
Topic |
|
Substring
