Asp Forum - Remove non alphabet characters from a column - microsoft.public.sqlserver.programming

Krishna

3/22/2007 2:37:00 PM

I have a column with data which have alphanumeric and some special
characters. How do I remove all the characters which are not alphabets [a-z]
or [A-Z]

9 Answers

TheSQLGuru

3/22/2007 2:43:00 PM

1) Use a CLR Regex type function to do it.

2) Contruct a while loop and iterate through each character of the data
building new data with inappropriate characters stripped out.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Krishna" <Krishna@discussions.microsoft.com> wrote in message
news:A787B3E8-E90A-4CFB-A0DC-98A2A89F803C@microsoft.com...
>I have a column with data which have alphanumeric and some special
> characters. How do I remove all the characters which are not alphabets
> [a-z]
> or [A-Z]

bob

3/22/2007 3:30:00 PM

Try this:

DROP TABLE #tmp
GO
CREATE TABLE #tmp ( test_col VARCHAR(50) )
GO
DROP TABLE #invalid_characters
GO
CREATE TABLE #invalid_characters ( invalid_char CHAR(1) PRIMARY KEY)
GO

INSERT INTO #invalid_characters
SELECT '`' UNION
SELECT '~' UNION
SELECT '!' UNION
SELECT '@' UNION
SELECT '#' UNION
SELECT '$' UNION
SELECT '%' UNION
SELECT '^' UNION
SELECT '&' UNION
SELECT '*' UNION
SELECT '+' UNION
SELECT '-' UNION
SELECT '=' UNION
SELECT '(' UNION
SELECT ')' UNION
SELECT '{' UNION
SELECT '}' UNION
SELECT '|' UNION
SELECT '\' UNION
SELECT '\' UNION
SELECT ':' UNION
SELECT ';' UNION
SELECT '''' UNION
SELECT '<' UNION
SELECT '>' UNION
SELECT ',' UNION
SELECT '?' UNION
SELECT '[' UNION
SELECT ']' UNION
SELECT '/' UNION
SELECT '\' UNION
SELECT '"'
GO

INSERT INTO #tmp
SELECT 'M@an thi!s is s#`ome rea-lly b=ad da<ta m:an' UNION
SELECT 'Hey I am ok' UNION
SELECT 'N^o$, t&hi+s- =o(n)e {}i||s: <worse> man'
GO

DECLARE @rowcount INT

-- Initialise
SET @rowcount = -1

WHILE @rowcount <> 0
BEGIN

UPDATE t
SET t.test_col = REPLACE( test_col, iv.invalid_char, '' )
FROM #tmp t CROSS JOIN #invalid_characters iv
WHERE CHARINDEX( iv.invalid_char, test_col ) <> 0

SET @rowcount = @@rowcount
END
GO

-- Fresh and clean
SELECT *
FROM #tmp

Let me know how you get on.

wBob

"Krishna" wrote:

> I have a column with data which have alphanumeric and some special
> characters. How do I remove all the characters which are not alphabets [a-z]
> or [A-Z]

Anith Sen

3/22/2007 3:59:00 PM

Some ideas at: www.projectdmx.com/tsql/strcleanup.aspx

--
Anith

Gert-Jan Strik

3/23/2007 5:28:00 PM

Another solution is something like this:

UPDATE my_table
SET my_column
=CASE WHEN substring(my_column,1,1) LIKE '[a-zA-Z]' THEN
substring(my_column,1,1) ELSE '' END
+CASE WHEN substring(my_column,2,1) LIKE '[a-zA-Z]' THEN
substring(my_column,2,1) ELSE '' END
+CASE WHEN substring(my_column,3,1) LIKE '[a-zA-Z]' THEN
substring(my_column,3,1) ELSE '' END
...

It's not pretty and you need to create one CASE WHEN line for each
character up to the maximum column size, but the query will run like
crazy, because everything can be done in one scan.

HTH,
Gert-Jan

Krishna wrote:
>
> I have a column with data which have alphanumeric and some special
> characters. How do I remove all the characters which are not alphabets [a-z]
> or [A-Z]

Sapient Fridge

7/19/2011 8:18:00 PM

In message <4e258dbf$0$22470$afc38c87@news.optusnet.com.au>, Oy Rool Out
a Carbon Tax <gillard_lies@promises.are.invalid> writes
>On 19/07/2011 9:53 PM, Sapient Fridge wrote:
>> In message <4e24d6ca$1@dnews.tpgi.com.au>, No AGW Yet Detected
>> <No@AGW.Yet.invalid> writes
>>> Where Did The Medi?val Warm Period Disappear To?
>>>
>>>
>>>
>>> If we go back to earlier IPCC reports...let's go back 20 years to about
>>> 1990, we can see graphs where there's a very prominent medi?val warm
>>> period.
>>>
>>>
>>>
>>> This is clearly presented in graphs in the early reports.
>>>
>>>
>>>
>>> But as we progress in time, the level of temperature during that
>>> historical
>>> period keeps declining, and so what they actually end up with is quite a
>>> famous graph...listeners may be aware of a graph that's referred to as
>>> the
>>> 'hockey-stick'
>>
>> Over time the data becomes more accurate. That's what happens in science.
>
>Why was this fact and the details associated with it not a "highlight"
>of the IPCC reports, which had the MWP becoming progressively smaller
>to the point of vanishing, over their successive reports. Just how much
>more "accurate" did this data "become" over the period from 1990, when
>the MWP was shown as patently warmer than the temperature regime of the
>20th century?
>http://www.realclimate.org/images/ipcc_1990_...
>Did they get a better tree ring measurer or something like that? A
>citation would be nice, from a reliable non-warmist site.

You would simply call any site that said that the MWP wasn't global a
"warmist site" and so dismiss it. Your shield of denial is
impenetrable.
--
sapient_usenet02@spamsights.org ICQ #17887309 * Save the net *
Grok: http://spam... http://www... * nuke a spammer *
Find: http://www.sa... http://www.ne... * today *
Kill: http://mail... http://au... http://sp...

Oy rool out a carbon tax

7/19/2011 10:55:00 PM

On 20/07/2011 6:17 AM, Sapient Fridge wrote:
> In message <4e258dbf$0$22470$afc38c87@news.optusnet.com.au>, Oy Rool Out
> a Carbon Tax <gillard_lies@promises.are.invalid> writes
>> On 19/07/2011 9:53 PM, Sapient Fridge wrote:
>>> In message <4e24d6ca$1@dnews.tpgi.com.au>, No AGW Yet Detected
>>> <No@AGW.Yet.invalid> writes
>>>> Where Did The Medi?val Warm Period Disappear To?
>>>>
>>>>
>>>>
>>>> If we go back to earlier IPCC reports...let's go back 20 years to about
>>>> 1990, we can see graphs where there's a very prominent medi?val warm
>>>> period.
>>>>
>>>>
>>>>
>>>> This is clearly presented in graphs in the early reports.
>>>>
>>>>
>>>>
>>>> But as we progress in time, the level of temperature during that
>>>> historical
>>>> period keeps declining, and so what they actually end up with is
>>>> quite a
>>>> famous graph...listeners may be aware of a graph that's referred to as
>>>> the
>>>> 'hockey-stick'
>>>
>>> Over time the data becomes more accurate. That's what happens in
>>> science.
>>
>> Why was this fact and the details associated with it not a "highlight"
>> of the IPCC reports, which had the MWP becoming progressively smaller
>> to the point of vanishing, over their successive reports. Just how
>> much more "accurate" did this data "become" over the period from 1990,
>> when the MWP was shown as patently warmer than the temperature regime
>> of the 20th century?
>> http://www.realclimate.org/images/ipcc_1990_...
>> Did they get a better tree ring measurer or something like that? A
>> citation would be nice, from a reliable non-warmist site.
>
> You would simply call any site that said that the MWP wasn't global a
> "warmist site" and so dismiss it. Your shield of denial is impenetrable.

OK. Any site will do, reliable or otherwise. In fact anything which
shows how more accurate data for the MWP materialized between 1990 and
now. We sceptical scientists like to see these things so that we can
assess them - that's why something from a reputable site is a good
start. A "me too" agenda-based blog provides nothing to study and
therefore nothing to dismiss. Just do your best.

--
"If we cut emissions today, global temperatures are not likely to drop
for about a thousand years. "
-- Tim (it ain't a gonna rain no more) Flannery
- Australian Climate Commissar

troppo

7/22/2011 3:18:00 AM

"Trevor Wilson" <trevor@rageaudio.com.au> wrote in
news:98rp17FvcjU3@mid.individual.net:

> Oy Rool Out a Carbon Tax wrote:
>> On 20/07/2011 6:17 AM, Sapient Fridge wrote:
>>> In message <4e258dbf$0$22470$afc38c87@news.optusnet.com.au>, Oy Rool
>>> Out a Carbon Tax <gillard_lies@promises.are.invalid> writes
>>>> On 19/07/2011 9:53 PM, Sapient Fridge wrote:
>>>>> In message <4e24d6ca$1@dnews.tpgi.com.au>, No AGW Yet Detected
>>>>> <No@AGW.Yet.invalid> writes
>>>>>> Where Did The Medi?val Warm Period Disappear To?
>>>>>>
>>>>>>
>>>>>>
>>>>>> If we go back to earlier IPCC reports...let's go back 20 years to
>>>>>> about 1990, we can see graphs where there's a very prominent
>>>>>> medi?val warm period.
>>>>>>
>>>>>>
>>>>>>
>>>>>> This is clearly presented in graphs in the early reports.
>>>>>>
>>>>>>
>>>>>>
>>>>>> But as we progress in time, the level of temperature during that
>>>>>> historical
>>>>>> period keeps declining, and so what they actually end up with is
>>>>>> quite a
>>>>>> famous graph...listeners may be aware of a graph that's referred
>>>>>> to as the
>>>>>> 'hockey-stick'
>>>>>
>>>>> Over time the data becomes more accurate. That's what happens in
>>>>> science.
>>>>
>>>> Why was this fact and the details associated with it not a
>>>> "highlight" of the IPCC reports, which had the MWP becoming
>>>> progressively smaller to the point of vanishing, over their
>>>> successive reports. Just how much more "accurate" did this data
>>>> "become" over the period from 1990, when the MWP was shown as
>>>> patently warmer than the temperature regime of the 20th century?
>>>> http://www.realclimate.org/images/ipcc_1990_...
>>>> Did they get a better tree ring measurer or something like that? A
>>>> citation would be nice, from a reliable non-warmist site.
>>>
>>> You would simply call any site that said that the MWP wasn't global
>>> a "warmist site" and so dismiss it. Your shield of denial is
>>> impenetrable.
>>
>> OK. Any site will do, reliable or otherwise. In fact anything which
>> shows how more accurate data for the MWP materialized between 1990
>> and now. We sceptical scientists like to see these things so that we
>> can assess them - that's why something from a reputable site is a
>> good start. A "me too" agenda-based blog provides nothing to study
>> and therefore nothing to dismiss. Just do your best.
>
> **
> http://www.newscientist.com/article/dn16892-natural-mechanism...
> val-warming-discovered.html

Article says "The finding scuppers one of the favourite arguments of
climate-change deniers ... ". Ok it's a warmist site.
Article quotes Michael Mann; in retrospect not the best choice in view of
his "Hockey-stick" infamy, and recent Climategate revelations (conspiracy
to hide MWP and MIA).

Mann is reported in the article as saying " ... volcanoes spouting cooling
ash into the atmosphere could have not only kicked off the medieval warming
...."

Interesting. Cooling causes warming. We have seen this recently, along with
variants, eg warming causes cooling, 13 years of stable then reducing
global temperatures is because sulpur emissions in China have increased,
so it is merely masking global warming.
It seems the usual suspects had these Catch 22s set up back in 2009.

Volcanic ash often has a measurable cooling effect (eg Mt Pinatubo 1991 -
1?C) and Icelandic eruptions during the Mini Ice Age appear to have been
similar, eg crop failures and famine in parts of Europe.

The article says " ... warm spell in Europe and meant wine could be
produced in England as it is now .. ".

Wine from grapes? The Romans knew grapes - and grew grapes at Vindalanda in
the far north of England. Don't think that would be possible now. We
managed a bit of elderberry wine in that area, 1974 - 78. Favourite crop
seemed to be leeks. No one grew grapes.

"Pacific La Ni?a mode and a positive NAO mode could have reinforced each
other in a positive feedback loop and this could explain the stability of
the medieval climate anomaly."

Uh-oh. Positive feedback is probably the worst error in the AGW creed. PF
is the justification for the 1.1?C to 4-5C blow-out in the GCMs. Even the
IPCC seems to have stopped supporting that. PF is extremely rare in nature,
and if it was a feature of climate, then a bit extra CO2 would be the least
of our worries.

I can't understand why so many intelligent people still believe this crap.

Ok - I have some expertise in the fields of mathematical modelling and
climate-responsive design. By the standards that appear to apply currently,
that makes me a "climate expert".

Regards

http://people.aapt.net.au...

B J Foster

7/22/2011 4:16:00 AM

On 22/07/2011 1:17 PM, troppo wrote:
> Interesting. Cooling causes warming. We have seen this recently, along with
> variants, eg warming causes cooling

Yeah but this is the first time we have seen 'Mann causes warming'.

Oy rool out a carbon tax

7/22/2011 4:37:00 AM

On 22/07/2011 2:16 PM, B J Foster wrote:
> On 22/07/2011 1:17 PM, troppo wrote:
>> Interesting. Cooling causes warming. We have seen this recently, along
>> with
>> variants, eg warming causes cooling
>
> Yeah but this is the first time we have seen 'Mann causes warming'.

But that cooling may be further evidence of warming... if it suits.

--
"If we cut emissions today, global temperatures are not likely to drop
for about a thousand years. "
-- Tim (it ain't a gonna rain no more) Flannery
- Australian Climate Commissar

microsoft.public.sqlserver.programming

Remove non alphabet characters from a column

Krishna

TheSQLGuru

bob

Anith Sen

Gert-Jan Strik

Sapient Fridge

Oy rool out a carbon tax

troppo

B J Foster

Oy rool out a carbon tax

x Login to ForumsZone