Sql Query for Specific columndata

1

Hi,

I have table like this excpet for 2 values,have to display samedata present in table for remaing 2 columns data will be displayed based on specific data.Data will be displayed as is if any type of data present in table except for Newzealan Gucklan,Newzealan Gucklan1.codes presnet in this columns may change and ists not static code values( like 10,20,30)

CREATE TABLE table1
(
Countrynamecode varchar(255),
);

Insert into table3 values ('Newzealan Gucklan(10)')
Insert into table3 values ('Newzealan Gucklan1(20)')
Insert into table3 values ('Newyork(30)')
Insert into table3 values ('Test')
Insert into table3 values ('Sample')

Output:
Countrynamecode
Gucnewz(10)
Gucnewz1(20)
Newyork(30)
Test
Sample

2 
select case when Countrynamecode like 'Newzealan %' 
           then substring(Countrynamecode, 10, 255) 
           else Countrynamecode 
       end as Countrynamecode
from table1
3

Hi Gbritton,

Thnaks for your reply.But im getting differnet output,
Gucklan(10)
Gucklan1(20)
Newyork(10)
Test
Sample

4

then you're not using the same input.

I copied this from your post:

CREATE TABLE table1
(
Countrynamecode varchar(255),
);

Insert into table3 values ('Newzealan Gucklan(10)')
Insert into table3 values ('Newzealan Gucklan1(20)')
Insert into table3 values ('Newyork(30)')
Insert into table3 values ('Test')
Insert into table3 values ('Sample')

Changed 'table3' to 'table1' (looks like a typo.) then ran my query. If you have different input, you'll get different output of course.

5

Hi Gbritton,

I have created new table with the above script but for first two records getting different output

QueryOutput ExpectdOutput
Gucklan(10) Gucnewz(10)
Gucklan1(20) Gucnewz1(20)
Newyork(30) Newyork(30)
Test Test
Sample Sample

6

why do you expect Guklan to change to Gucnewz? do you want all codes with 'lan' in them to have 'lan' replaced with 'newz'? If not, what is the criteria?

7

I have to pick After Space first3charcters and starting 4letters for Newzealan Gucklan and
first3charcters and last numeric value and starting 4letters for Newzealan Gucklan1.

8

I'm really struggling here to see the rule. e.g if you had:

Donald Trump(45)
Donald Trump2(46)

would you expect

TruDona(45)
TruDona2(46)

in the output?

What if you also had:

Hillary Clinton
Hillary Clinton Wife of Bill 42 (70)

what would you expect to see?

9

every time i expect trump scenario data exactly for remaing it should be as usual.So here my case every time i expect Newzealan Gucklan,Newzealan Gucklan1 are special data and rest we will not consider for that rule

10

"every time i expect trump scenario data exactly for remaing it should be as usual."

Whew! a bit tough to parse that! BTW what's your first language?

I think you may want something like this:

select case when Countrynamecode like 'Newzealan Gucklan%' 
		   then substring(Countrynamecode, 11, 3) 
                        + substring(Countrynamecode, 1, 4)
			+ substring(Countrynamecode, 18, 255)
           else Countrynamecode 
	   end as Countrynamecode
from table1
1 Like
11

Thanks gbritton.your code works for me.Replace function also working..

SELECT REPLACE ( countrynamecode,'Newzealan Gucklan','Gucnewz') AS output
FROM table6 1

12

Yes, replace would work, but would also modify:

Donald Newzealan Gucklan Trump