How to fing first letter is a number or letter in a column

Question

Post reply

How to fing first letter is a number or letter in a column

VG-619426

Hall of Fame

Points: 3914
More actions
April 7, 2010 at 9:14 am

#143914

Hi,
How to find the first letter is a character or number in a column?
My column has s12345, 345678, n789054
Thank you,
VG

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

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

test for numeric:

ISNUMERIC(LEFT(yourFieldName,1)) = 1

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

VG-619426 Hall of Fame Points: 3914 More actions · Answer 2

Sorry, I would have explained little more about my situation.

I want to read the column value along with some other select statements.

select column1, column2,

case column3

when countrycode = 'US' then select the value from the column which starts with a character like 's12345'

else

column3

Thanks again..

VG

WayneS SSC Guru Points: 95460 More actions · Answer 3

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 4

This should be what you're looking for. If not we'll need some more information like Wayne said. There's a link in his signature on getting better assistance that explains the best way to post test data.

select column1, column2,

case

when countrycode = 'US' then ISNUMERIC(LEFT(column3,1))

else column3

end 'columnalias'

from tablename

LutzM SSC Guru Points: 107049 More actions · Answer 5

I'd rather test for a number instead of using the ISNUMERIC function.

Among others, "+-.," are valid when testing against ISNUMERIC

Example:

DECLARE @test-2 CHAR(3)

SET @test-2 ='.10'

SELECT ISNUMERIC(LEFT(@test,1)),CASE WHEN @test-2 LIKE '[0-9]%' THEN 1 ELSE 0 END

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 6

Jason Selburg (4/7/2010)
test for numeric:
ISNUMERIC(LEFT(yourFieldName,1)) = 1

Heh... you sure about that ol' friend?

SELECT ISNUMERIC(LEFT('$Howdy',1)),

ISNUMERIC(LEFT(',Howdy',1)),

ISNUMERIC(LEFT('.Howdy',1)),

ISNUMERIC(LEFT('+Howdy',1)),

ISNUMERIC(LEFT('-Howdy',1)),

ISNUMERIC(LEFT(CHAR(9)+'Howdy',1))

Lutz has the correct idea. Never use ISNUMERIC as an ISALLDIGITS function because that's not what it is.

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

Jeff Moden (4/7/2010)
Jason Selburg (4/7/2010)
test for numeric:
ISNUMERIC(LEFT(yourFieldName,1)) = 1
Heh... you sure about that ol' friend?
SELECT ISNUMERIC(LEFT('$Howdy',1)),
ISNUMERIC(LEFT(',Howdy',1)),
ISNUMERIC(LEFT('.Howdy',1)),
ISNUMERIC(LEFT('+Howdy',1)),
ISNUMERIC(LEFT('-Howdy',1)),
ISNUMERIC(LEFT(CHAR(9)+'Howdy',1))
Lutz has the correct idea. Never use ISNUMERIC as an ISALLDIGITS function because that's not what it is.

😛

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Try this one...

declare @n varchar(10)

set @n ='10'

select case when ascii(left(@n,1)) > 47 and ascii(left(@n,1)) < 58 then 1 else 0 end

karthik

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Is this too simple??

Where Col LIKE '[0-9]%'

That's my favorite for clarity...

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

More than one way to skin this cat correctly, and probably more than one way to do it INcorrectly as well! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service