Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query Help needed...

Answered Query Help needed...

  • Friday, February 15, 2013 5:23 AM
     
     
    Sign In to Vote
    0

    Hi all,

    my table is like this

    COLUMN 1

    COLUMN 2

    ap10

    ap10

    ap11

    ap11

    ap12

    ap12

    mp10

    mp10

    mp11

    mp11

    mp12

    mp12

    mp13

    mp13

    123

    jh01

    345

    jh02

    546

    pj07

    .

    ap12

    ,,

    rj01

    NULL

    rj02

     

    where ever column 1 is junk(in this case 123,345,546,null are junk and [alpha][alpha][number][number] is valid one)

    and my output should be like this 

    COLUMN 1

    ap10

    ap11

    ap12

    mp10

    mp11

    mp12

    mp13

    jh01

    jh02

    pj07

    ap12

    rj01

    rj02

    Thanks and Regards

     

All Replies

  • Friday, February 15, 2013 5:32 AM
     
     
    Sign In to Vote
    0

    where ever column 1 is junk(in this case 123,345,546,null are junk and [alpha][alpha][number][number] is valid one

    Can you explain rjo2 in output ???

    Thanks and regards, Rishabh K

     
  • Friday, February 15, 2013 5:35 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    Not sure exactly what you are looking for, but perhaps

    Declare @Test Table(Column1 varchar(10), Column2 varchar(10));
Insert @Test(Column1, Column2) Values
('ap10', 'ap10'),
('ap11', 'ap11'),
('ap12', 'ap12'),
('mp10', 'mp10'),
('mp11', 'mp11'),
('mp12', 'mp12'),
('mp13', 'mp13'),
('123', 'jh01'),
('345', 'jh02'),
('546', 'pj07'),
('.', 'ap12'),
(',,', 'rj01'),
(NULL, 'rj02');

Select Case When Column1 Like '[a-z][a-z][0-9][0-9]' Then Column1 Else Column2 End As Column1
From @Test;

    Tom

    • Proposed As Answer by Rishabh K Friday, February 15, 2013 5:40 AM
    •  
     
  • Friday, February 15, 2013 5:35 AM
     
     
    Sign In to Vote
    0

    Hi ,

    rj02=[alpha][alpha][number][number] its a code in "column 2"

    Thanks and Regards

     
  • Friday, February 15, 2013 5:38 AM
     
     
    Sign In to Vote
    0

    Try

    select distinct case when column1 is null then case when len(column1)<len(column2) then column2 else column1 end else case when len(column1)<len(column2) then column2 else column1 end end column1

    from tab1

    order by 1;

    Many Thanks & Best Regards, Hua Min



     
  • Friday, February 15, 2013 5:38 AM
     
      Has Code
    Sign In to Vote
    0

    Hi

    Try this Query

    SELECT * FROM tableName
WHERE column1 LIKE '[!0123456789.,]%' or column1 IS NOT NULL

    PS.Shakeer Hussain

     
  • Friday, February 15, 2013 5:40 AM
     
     
    Sign In to Vote
    0

    Hi ,

    rj02=[alpha][alpha][number][number] its a code in "column 2"

    Thanks and Regards

    Refer to Tom's post 

    Thanks and regards, Rishabh K

     
  • Friday, February 15, 2013 5:42 AM
     
      Has Code
    Sign In to Vote
    0

    Try the below:

    Create Table T1(Val Varchar(10))
Insert into T1 Select 'as12'
Insert into T1 Select 'df22'
Insert into T1 Select '12'
Insert into T1 Select NULL
Select * From T1 
Where  Val Like '[a-z][a-z][0-9][0-9]' 
and Val is not null
    EDIT: I didnt see Tom's post when I submit. Please refer Tom's Post

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


     
  • Friday, February 15, 2013 6:06 AM
     
     
    Sign In to Vote
    0

    Thanks a lot ....

    COLUMN 1

    COLUMN 2

    COLUMN 3

    ap10

    ap10

    andhra

    ap11

    ap11

    andhra

    ap12

    ap12

    andhra

    mp10

    mp10

    madyapradesh

    mp11

    mp11

    madyapradesh

    mp12

    mp12

    madyapradesh

    mp13

    mp13

    madyapradesh

    123

    jh01

    jharkhand

    345

    jh02

    jharkhand

    546

    pj07

    punjab

    .

    14

    andhra

    ,,

    #

    rajastan

    NULL

    13

    rajastan

    if i get one more condition.can tell me how to write case for this

    output sholud be like this

    COLUMN 1

    ap10

    ap11

    ap12

    mp10

    mp11

    mp12

    mp13

    jh01

    jh02

    pj07

    andhra

    rajastan

    rajastan

    Many Thanks

     
  • Friday, February 15, 2013 6:14 AM
     
     Answered Has Code
    Sign In to Vote
    0
     
    Select Case When Column1 Like '[a-z][a-z][0-9][0-9]' Then Column1 When Column2 Like '[a-z][a-z][0-9][0-9]' Then Column2 Else Column3 End

    Tom
     
  • Friday, February 15, 2013 6:22 AM
     
     
    Sign In to Vote
    0

    Thanks a lot ....

    COLUMN 1

    COLUMN 2

    COLUMN 3

    ap10

    ap10

    andhra

    ap11

    ap11

    andhra

    ap12

    ap12

    andhra

    mp10

    mp10

    madyapradesh

    mp11

    mp11

    madyapradesh

    mp12

    mp12

    madyapradesh

    mp13

    mp13

    madyapradesh

    123

    jh01

    jharkhand

    345

    jh02

    jharkhand

    546

    pj07

    punjab

    .

    14

    andhra

    ,,

    #

    rajastan

    NULL

    13

    rajastan

    if i get one more condition.can tell me how to write case for this

    output sholud be like this

    COLUMN 1

    ap10

    ap11

    ap12

    mp10

    mp11

    mp12

    mp13

    jh01

    jh02

    pj07

    andhra

    rajastan

    rajastan

    Many Thanks

    Did you try my script above?

    Many Thanks & Best Regards, Hua Min

     
  • Friday, February 15, 2013 6:39 AM
     
     Answered Has Code
    Sign In to Vote
    0

    If I have understood your problem correctly, the below T-SQL will give you the expected output - 

    SELECT Column2 FROM YourTable
    Hope, this helps!

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia