Select Rows Where field is text

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Rows Where field is text

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2011-06-28 : 12:02:40

Been looking for a way to only select rows where a field is text.
Some will be numeric some will not.

Reason is, fixing a db from another.
AddedBy field has both numeric - from the user table (the correct way) and text - the actual fname and lname of a user.

I need to update and make all numeric for a userID.

Here's is what I have so far:


UPDATE t2 
SET AddedBy = t1.USERID 
FROM CUSTOMERINFO t2
INNER JOIN USER_INFO t1 ON t2.AddedBy = t1.FName + ' ' + t1.LName
--WHERE ISNUMERIC(AddedBy) = 1

But also after this is run, need to update the field again to NULL if it is text.
Suggestions?

Thanks,

Zath

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 12:16:22

What do you mean by numeric
as far as isnumeric is concerned 0D1 and 0E1 are both numeric.
If you want integers use fld like '%[^0-9]%'

UPDATE CUSTOMERINFO
SET AddedBy = t1.USERID
FROM CUSTOMERINFO t2
INNER JOIN USER_INFO t1
ON t2.AddedBy = t1.FName + ' ' + t1.LName
WHERE AddedBy like '%[^0-9]%'

Or you can omit the where clause as presumably there will never be a match?
Is USERID numeric - might want to do an explicit convert

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2011-06-28 : 12:18:38

Well I just ran the script as it is (without the where clause) and it did work ok.

But there were a couple not found, so it left the name.

So, I need to do another update and set only text values = NULL on the field.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2011-06-28 : 12:26:48

I got it working!

Used your WHERE clause for the final update:

UPDATE CUSTOMERINFO SET AddedBy = NULL
WHERE AddedBy like '%[^0-9]%'

Thanks nigelrivett!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources