Select records with Alphanumeric Values

Question

Post reply

Select records with Alphanumeric Values

Paul_Harvey

Hall of Fame

Points: 3822
More actions
April 16, 2010 at 5:15 am

#233892

I am selecting data from a table using
Select id = NULLIF(SPACE(0)) etc.
I did this because I did not want any blank fields in my SSIS dataflow, and ultimately in my production database which the SSIS package populates. I have now realised that values in some fields without alphanumeric characters, for example dots, dashes, question marks etc, are causing me problems too.
I need to change values in three fields to NULL in the following circumstances:
1) They are blank, but not null
2) They do not contain alphanumeric characters.
So values with non-alpha characters are fine, provided that there are alpha characters along with it.
"St. John" is allowable, but "." is not.
"Piper-Smith" is allowable but "-" is not.
Can anyone help me with a statement to either update the three fields to NULL if they don't contain ANY alphanumeric characters, or to somehow amend the original select NULLIF statement to do the same?
I can do somethiing like this:
Update Table
Set Field = NULL where field not like '%A%' or field not like '%B%'
Etc
But I am sure there is a more succinct way to address this.
Any help appreciated,
Paul

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

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

hi ,

I didnt get Ur Problem .....& not good in SSIS....

As per Ur query use replace function .....

ex: Replace(strcolumn,'?',' ')

replace the unwanted chars with space or '' [without space]...

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 2

Hi Sasidhar

Thanks for your advice.

Unfortunately I don't have a full list of disallowed fields, only allowable ones!

Perhaps my oringal post was a little elongated. What I am looking for is a way to identify values in a clumn that do NOT contain characters 0 to 9 or A to Z.

Paul

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Hi Buddy,how about you going through this following article and helping us help you?? 🙂

CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

When u do so, i am sure a lot of us will help u instantly...

So please post

1. Table Structures - CREATE TABLE scripts

2. Constraints or Indexes in the table, - CREATE SCRIPTS

3. SAMPLE DATA - INSERT INTO TABLE scripts

4. Desired output - some visual representation of this.

We could guess very little information from your post mate 🙁 !!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

How about this code buddy??

This will select the rows of a column that dont contain characters from 0 to 9 and a to z

DECLARE @STRING TABLE (STRING VARCHAR(100))

INSERT @STRING

SELECT 'ABCD___123==A1B2C3+++'

UNION ALL

SELECT 'ABCD+++'

UNION ALL

SELECT '+++'

UNION ALL

SELECT 'ABCD '

UNION ALL

SELECT '1234'

SELECT * FROM @STRING

SELECT STRING FROM @STRING

WHERE

STRING NOT LIKE '%[0-9]%'

AND

STRING NOT LIKE '%[A-Z]%'

Please tell us if that works for you!!

Cheers!!

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

DECLARE @teststring VARCHAR(65)

SELECT @teststring = 'St.John'

IF PATINDEX('%[A-Z,0-9]%',@teststring) > 0

PRINT @teststring

ELSE

PRINT NULL

-edit-

Didn't see the post above me before I hit submit. I prefer mine, but I'm not a SQL developer - I'm a .net developer so chances are there'll be a reason why mine is incorrect 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 6

Hi All,

Many thanks for your attempts to assist me.

I am a little embarassed to have been given the link to posting etiquette. My apologies.

I didn't spend enough time structuring my question.

I will try again:

The following will populate some records to use:

DECLARE @Name TABLE (Name VARCHAR(100))

INSERT @Name

SELECT 'St. John'

UNION ALL

SELECT 'Piper-Smith'

UNION ALL

SELECT '...'

UNION ALL

SELECT ' '

UNION ALL

SELECT '??'

UNION ALL

SELECT '!Jones'

UNION ALL

SELECT '!£'

My current select statement is

SELECT

NAME = NULLIF(Name,SPACE(0))

FROM @NAME

This returns the following:

St. John

Piper-Smith

...

NULL

??

!Jones

!£

I am using the NULLIF because it works very quickly, and prevents me from importing Blank fields into my final dataset.

I have recently discovered that field values that do not contain 0-9 or a-z are also causing me problems, so I need to amend my select statement to return NULL if the value does not contain 0-9 or a-z, or if it is blank.

The result set that I am looking for in this case is:

St. John

Piper-Smith

NULL

!Jones

NULL

I hope this is clearer.

Thanks,

Paul

I hope this is a little clearer.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Hello paul, dont feel embarassed ..

Did u try using my code, Paul?

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 8

Hello,

Thanks for your code.

Yes, I have tried it,and I have been trying to modify it to suit my purposes.

Your code is selecting the records that I want, but is not returning NULL for the other records.

I need to return a row for each record, but with NULL if there are no alphanum characters, or only spaces.

I will keep trying. Thanks for the pointer.

Paul

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

Cool, Paul! 🙂

Am not at my desk right now, so when i reach upstairs, i will work on your ask mate 🙂

Meanwhile, as u said , keep trying :cool:!!

Cheers!!

Paul_Harvey Hall of Fame Points: 3822 More actions · Answer 10

Ok. What I have done is to add another Execute SQL task in SSIS as follows:

Update TABLE

Set NAME = NULL

where NAME NOT LIKE '%[0-9]%'

AND

NAME NOT LIKE '%[A-Z]%'

This does what I wanted it to do. Thanks for the pointer on this.

The end result is now what I wanted it to be.

I was hoping that somehow this could be done without further database calls, but amending the original Select NULLIF statement, but this is not to be!

However the end result is the most important, as these values were crashing SSIS.

Thanks for all the assistance.

Paul

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 11

Glad to have helped you and happy that your issue is resolved 😉 😎

Cheers!!