Retrieving only character result set from varchar column

Question

Post reply

Retrieving only character result set from varchar column

farooq.hbs

SSCrazy

Points: 2083
More actions
December 14, 2012 at 3:18 am

#268105

Hi guys,
I have an employee table, in this table there is a column called employeeId(Varchar(5000)).
Now i want only to retrieve the result set of char type
Exapmle
EmployeeId
123
ABC
259
CSD
GFRG
EWSF
369
Soluntion:
I want only ABC,CSD,GFRG,EWSF from the above table excluding the numbers

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Like this:

SELECT *

FROM employee

WHERE employeeId LIKE '%[A-Z]%'

AND employeeId NOT LIKE '%[0-9]%'

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

If employeeID does not contain combination of both alphabets and numerals than you can use this also:

SELECT *

FROM employee

WHERE employeeId LIKE '%[A-Z]%'

In the earlier post, I assumed that employeeId can be a combination of both alphabets and numerals. e.g. ABC123

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

farooq.hbs (12/14/2012)
Hi guys,
I have an employee table, in this table there is a column called employeeId(Varchar(5000)).
Now i want only to retrieve the result set of char type
Exapmle
EmployeeId
123
ABC
259
CSD
GFRG
EWSF
369
Soluntion:
I want only ABC,CSD,GFRG,EWSF from the above table excluding the numbers

I have to ask... why in the world are you folks using a VARCHAR(5000) for an EmployeeID column that probably shouldn't ever be larger that 50 or so characters. It's really not good for indexing and could easily go "out of row" if any given row exceeds about 8,060 bytes in total because of other columns built in such a fashion. Don't forget... that includes ANY table that includes this column as the same datatype.

--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: 1004339 More actions · Answer 4

Lokesh Vij (12/14/2012)
If employeeID does not contain combination of both alphabets and numerals than you can use this also:
SELECT *
FROM employee
WHERE employeeId LIKE '%[A-Z]%'
In the earlier post, I assumed that employeeId can be a combination of both alphabets and numerals. e.g. ABC123

Oh, be careful now. That won't exclude names with numbers in them. You'll need to use some "double NOTs". If you want rows that contain ONLY alphabetic characters, then the following will do the trick.

SELECT *

FROM employee

WHERE employeeId NOT LIKE '%[^A-Z]%'

The circumflex is another "NOT".

--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)