Substring Records

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  December 7, 2012 at 4:22 am

  #393762

  Hi Expert,

  I want to show only AFG,USA,INDfrom my below table..

  CREATE TABLE test(

  [ENAME] [varchar](30) NULL,

  )

  insert into test values ('Afghanistan [AFG]')

  insert into test values('Brazil [BRA]')

  insert into test values('United States [USA]')

  insert into test values('France [FRA]')

  insert into test values('India [IND]')

  insert into test values('Algeria [DZA]')

  insert into test values('Algeria [DZA]')

  insert into test values('United States [USA]')

  insert into test values('Bangladesh [BGD]')

  So request you to please provide me solution.

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 7, 2012 at 4:36 am

  #1566553

  SELECT ENAME

  FROM test

  WHERE PATINDEX('%[[[]AFG]', ENAME) > 0

  OR PATINDEX('%[[[]USA]', ENAME) > 0

  OR PATINDEX('%[[[]IND]', ENAME) > 0;

  Results in: -

  ENAME

  ------------------------------

  Afghanistan [AFG]

  United States [USA]

  India [IND]

  United States [USA]

  SELECT SUBSTRING(ENAME,PATINDEX('%[[][A-Z][A-Z][A-Z]]', ENAME),LEN(ENAME))

  FROM test;

  Results in: -

  ------------------------------

  [AFG]

  [BRA]

  [USA]

  [FRA]

  [IND]

  [DZA]

  [DZA]

  [USA]

  [BGD]

  Can you manage from there?

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  December 7, 2012 at 7:26 pm

  #1566817

  kiran.rajenimbalkar (12/7/2012)

  ---

  Hi Expert,

  I want to show only AFG,USA,INDfrom my below table..

  CREATE TABLE test(

  [ENAME] [varchar](30) NULL,

  )

  insert into test values ('Afghanistan [AFG]')

  insert into test values('Brazil [BRA]')

  insert into test values('United States [USA]')

  insert into test values('France [FRA]')

  insert into test values('India [IND]')

  insert into test values('Algeria [DZA]')

  insert into test values('Algeria [DZA]')

  insert into test values('United States [USA]')

  insert into test values('Bangladesh [BGD]')

  So request you to please provide me solution.

  Cadavre's solution works but to keep from having to write such code, it would be much better if you normalized the data you have above and put it into two columns.

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