Alpha Numeric Column

• Charmer

  SSChampion

  Points: 14903

  More actions

  November 1, 2011 at 8:02 am

  #245429

  Hi Friends,

  i have one aplha numeric column...

  i need a query which gives only the numeric first and then alpha characters...

  for example my column is having data like 5a, ab45, 1230, 10B, BAC7...

  i need only 5a and 10B....

  Thanks,
  Charmer

• Mark Cowne

  One Orange Chip

  Points: 26969

  More actions

  November 1, 2011 at 8:13 am

  #1401631

  Something like this?

  DECLARE @t TABLE(Col VARCHAR(20))

  INSERT INTO @t(Col)

  SELECT '5a' UNION ALL

  SELECT 'ab45' UNION ALL

  SELECT '1230' UNION ALL

  SELECT '10B' UNION ALL

  SELECT 'BAC7'

  SELECT Col

  FROM @t

  WHERE Col LIKE '[0-9]%[A-Z]%';

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Charmer

  SSChampion

  Points: 14903

  More actions

  November 1, 2011 at 8:15 am

  #1401635

  that's what i need on....thank you so much buddy...

  Thanks,
  Charmer

• Ant-Green

  SSC Guru

  Points: 113509

  More actions

  November 1, 2011 at 8:17 am

  #1401639

  a bit crude, but you get the idea

  create table a (a varchar(10))

  go

  insert into a values ('123abc');

  insert into a values ('1023');

  insert into a values ('12dcrttg');

  insert into a values ('frtgwef');

  insert into a values ('102');

  insert into a values ('78dfdf');

  insert into a values ('gfrg');

  go

  select * from a where a like '[0-9]%' and A like '%[a-zA-Z]'

• Ant-Green

  SSC Guru

  Points: 113509

  More actions

  November 1, 2011 at 8:17 am

  #1401640

  doh, beat me to it