Select Only Rows with Alphabetical Character in Column

Question

Post reply

Select Only Rows with Alphabetical Character in Column

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
April 17, 2014 at 12:31 pm

#292461

Hello Everyone
I hope that all is going well.
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
Some of the data is:
1.5008e+015
1.54453e+015
1.51922e+015
1.51922e+015
1.52243e+015
but there is a mix of alphabetical characters in there as well.
1.51922e+015
1.53122e+015
FMCIT
ABCNP
FMCPNG
1.62073e+015
1.6127e+015
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
Thank you very much in advance for all your suggestions, comments, code samples and time.
Andrew SQLDBA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

gbritton1

SSCertifiable

Points: 6522

April 17, 2014 at 12:41 pm

#1706900

Can't you use the ISNUMERIC function?

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

AndrewSQLDBA

SSC-Insane

Points: 21827

April 17, 2014 at 1:02 pm

#1706903

Thanks

I have already tried that.

Andrew SQLDBA

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Do you want any rows that do not contain any numbers? It is not entirely clear what you want here.

with someData as

(

select '1.51922e+015' as SomeValue union all

select '1.53122e+015' union all

select 'FMCIT' union all

select 'ABCNP' union all

select 'FMCPNG' union all

select '1.62073e+015' union all

select '1.6127e+015'

)

select *

from someData

where SomeValue not like '%[0-9]%'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Or maybe this??

where ISNUMERIC(SomeValue) = 0

ISNUMERIC is rather useless because it can return some false positives too. http://www.sqlservercentral.com/articles/ISNUMERIC%28%29/71512/[/url]

Make sure you read to discussion too.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

or:

with someData as

(

select '1.51922e+015' as SomeValue union all

select '1.53122e+015' union all

select 'FMCIT' union all

select 'ABCNP' union all

select 'FMCPNG' union all

select '1.62073e+015' union all

select '1.6127e+015'

)

select *

from someData

where 0 = isnumeric(SomeValue)

yields:

FMCIT

ABCNP

FMCPNG

ScottPletcher SSC Guru Points: 101118 More actions · Answer 6

WHERE

column_name LIKE '[a-z]%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

ScottPletcher (4/17/2014)
WHERE column_name LIKE '[a-z]%'

That won't reject strings like '1.62073e+015'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 8

Jeff Moden (4/17/2014)
ScottPletcher (4/17/2014)
WHERE column_name LIKE '[a-z]%'
That won't reject strings like '1.62073e+015'

It won't?? That string doesn't look like it starts with a letter to me ... are you perhaps reading in a leading '%' that isn't there :hehe:?

SELECT column_name

FROM (

SELECT '1.62073e+015' AS column_name UNION ALL

SELECT 'a1.62073e+015'

) AS test_data

WHERE

column_name LIKE '[a-z]%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 9

Thanks Everyone

Greatly appreciate all the great code samples, and suggestions

I did not any luck with the ISNUMERIC, I tried that first.

Andrew SQLDBA

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

ScottPletcher (4/17/2014)
Jeff Moden (4/17/2014)
ScottPletcher (4/17/2014)
WHERE column_name LIKE '[a-z]%'
That won't reject strings like '1.62073e+015'
It won't?? That string doesn't look like it starts with a letter to me ... are you perhaps reading in a leading '%' that isn't there :hehe:?

Guh! That was it. Old eyes didn't see that right. Thanks Scott.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

To make sure the you won't have a problem in the future with strings like 'SomeString1' or any other mix of characters including symbols especially in some of the collations available, etc, try the following (added test data to that which was already posted)...

WITH SomeData(SomeValue) AS

(

SELECT '1.51922e+015' UNION ALL

SELECT '1.53122e+015' UNION ALL

SELECT 'FMCIT' UNION ALL

SELECT 'ABCNP' UNION ALL

SELECT 'FMCPNG' UNION ALL

SELECT '1.62073e+015' UNION ALL

SELECT '1.6127e+015' UNION ALL

SELECT 'A+B' UNION ALL

SELECT 'A.B' UNION ALL

SELECT 'A B' --Space between letters

)

SELECT *

FROM someData

WHERE SomeValue NOT LIKE '%[^A-Za-z]%' COLLATE LATIN1_GENERAL_BIN

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)