CODEWITHSUNDEEP
sql-servert-sql
CooL i3oY
CooL i3oY

Reputation: 800

Fine Alphabet in number in SQL

i have a table like this :

CREATE TABLE [Mytable](
    [Name] [varchar](10),
    [number] [nvarchar](100) )

i want to find [number]s that include Alphabet character?

data must format like this:

Name | number
---------------
Jack | 2131546
Ali  | 2132132154

but some time number insert informed and there is alphabet char and other no numeric char in it, like this:

Name | number
---------------
Jack | 2[[[131546ddfd
Ali  | 2132*&^1ASEF32154

i wanna find this informed row. i can't use 'Like' ,because 'Like' make my query very slow.

Upvotes: 0

Views: 635

Answers (2)

Ben Thul
Ben Thul

Reputation: 32687

A bit outside the box, but you could do something like:

  1. bulk copy the data out of your table into a flat file
  2. create a table that has the same structure as your original table but with a proper numeric type (e.g. int) for the [number] column.
  3. bulk copy your data into this new table, making sure to specify a batch size of 1 and an error file (where rows that won't fit the schema will go)
  4. rows that end up in the error file are the rows that have non-numerics in the [number] column

Of course, you could do the same thing with a cursor and a temp table or two...

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452978

Updated to find all non numeric characters

select * from Mytable where number like '%[^0-9]%'

Regarding the comments on performance maybe using clr and regex would speed things up slightly but the bulk of the cost for this query is going to be the number of logical reads.

Upvotes: 5

Related Questions