Getting string part based on some character in MSSQL

Hi,
I have one Req where I have to get the portion of the string ,like I have one e-Mail iD "shivendra.narayan@.rediffmail.com". I need only rediffmail.com now. Means to say get the string after the '@.'

PLZ help me

Thank You,
Shivendra

Quote:

Originally Posted by shivendra

Hi,
I have one Req where I have to get the portion of the string ,like I have one e-Mail iD "shivendra.narayan@.rediffmail.com". I need only rediffmail.com now. Means to say get the string after the '@.'

PLZ help me

Thank You,
Shivendra

-----------

Declare string_index int
Declare col_length int
select string_index = PATINDEX('%@.%', columnname),
col_length = length(columnname)
FROM tablename
WHERE 'Write your condition here'
Now, you can use the query below to return the
SELECT SUBSTRING(columnname, string_index, (col_length - string_index))
from tablename WHERE 'Write your condition here'

Hope this helps!!
Thanks!
Santhosh

Getting rid of unwanted characters!

Hello Gurus, I’ve a table that has a column with a text data type. When th
ey
imported the data from a different system some non ASCII character slipped
into the table. What would be the best way to get rid of them?
For example: A man ne￡ds ??help. to A man needs help
thanks in advance.Hi
If this is a one off, you may want to try:
UPDATE myStrings
SET str = STUFF(str,PATINDEX('%[^A-Z ]%',str),1 ,'')
WHERE PATINDEX('%[^A-Z ]%',str) > 0
WHILE @.@.ROWCOUNT > 0
BEGIN
UPDATE myStrings
SET str = STUFF(str,PATINDEX('%[^A-Z ]%',str),1 ,'')
WHERE PATINDEX('%[^A-Z ]%',str) > 0
END
John
"KB" wrote:

> Hello Gurus, I’ve a table that has a column with a text data type. When
they
> imported the data from a different system some non ASCII character slipped
> into the table. What would be the best way to get rid of them?
> For example: A man ne￡ds ??help. to A man needs help
> thanks in advance.
>|||http://www.aspfaq.com/2445
"KB" <KB@.discussions.microsoft.com> wrote in message
news:F11D992A-CC05-4776-B43A-A3AB8412F291@.microsoft.com...
> Hello Gurus, I’ve a table that has a column with a text data type. When
> they
> imported the data from a different system some non ASCII character slipped
> into the table. What would be the best way to get rid of them?
> For example: A man ne￡ds ??help. to A man needs help
> thanks in advance.
>|||Great Help! Thank you gentlemen.
"KB" wrote:

> Hello Gurus, I’ve a table that has a column with a text data type. When
they
> imported the data from a different system some non ASCII character slipped
> into the table. What would be the best way to get rid of them?
> For example: A man ne￡ds ??help. to A man needs help
> thanks in advance.
>

Getting nulls in SQL2005 table while importing from EXCEL spreadsheet

I am trying to import an Excel Spreadsheet into SQL2005. There is a column in the spreadsheet that has character values, and numbers. I have formatted the numbers as text on the spreadsheet. I have declared the column on the table as char/varchar/nchar, but whatever I do, the numbers don't get imported into the table, but show up as nulls. Any idea why?

Thanks

Mangala

Search the forum. It's a common issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1872950&SiteID=1