## 10 replies

Last post Aug 12, 2017 05:44 AM by Jayesh.Dalvi

• Jayesh.Dalvi

Member

185 Points

297 Posts

Hi,

Please help me to find a MS SQL query for numbers having common digits to find below result:

IF data In database for Table emp is as given below:

EMPID |EMPNUMBER
1          |111

2          |222

3          |333

4          |444

Now IF I Want to update 222 value in EMPNUMBER for EMPID 2 with 224 just because

Once again If I please Want to update 333 value in EMPNUMBER for EMPID 3 with 334 just because

I need something like below:

Update emp set EMPNUMBER (333) = 334 where 333 and 334 have common 33

Thanks,

Regards,

Jayesh(From India,Mumbai)

• JBetancourt

Participant

1370 Points

608 Posts

when you say both numbers, what are the 2 numbers, could you rephrase your question?

for example where did you get the 224 and 334 from, are those parameters or what?

-----------------
• asyed4u

Participant

1284 Points

373 Posts

#### Jayesh.Dalvi

333 and 334 have common 33

```create table #temp_cd(
EMPid int,
EMPNUMBER int)

insert into #temp_cd values (1,111)
insert into #temp_cd values (2,222)
insert into #temp_cd values (3,330)
insert into #temp_cd values (4,331)
insert into #temp_cd values (5,332)
insert into #temp_cd values (6,333)
insert into #temp_cd values (7,444)

select * From #temp_cd
declare @user_input int=33

select NextUpdate=MAX(EMPNUMBER)+1
From #temp_cd
where substring(cast(EMPNUMBER as varchar(3)),1,2)=@user_input

drop table #temp_cd```

• asyed4u

Participant

1284 Points

373 Posts

```create table #temp_cd(
EMPid int,
EMPNUMBER int)

insert into #temp_cd values (1,111)
insert into #temp_cd values (2,222)
insert into #temp_cd values (3,333)
insert into #temp_cd values (4,444)

declare @user_input int=334
select * From #temp_cd

update #temp_cd set EMPNUMBER= @user_input where substring(cast(EMPNUMBER as varchar(3)),1,2)=substring(cast(@user_input as varchar(3)),1,2)
select * From #temp_cd

drop table #temp_cd```

• A2H

All-Star

50741 Points

9877 Posts

#### Jayesh.Dalvi

If I please Want to update 333 value in EMPNUMBER for EMPID 3 with 334 just because

You can find the row to update based on EMPId column instead of EMPNumber column

``` UPDATE YourTable
SET EMPNUMBER = 334 -- Your new value here
WHERE EMPid	 = 3 -- Provide the EmpID here```

Demo Code

```Declare @temp_cd TABLE(
EMPid int,
EMPNUMBER int)

insert into @temp_cd values (1,111)
insert into @temp_cd values (2,222)
insert into @temp_cd values (3,330)
insert into @temp_cd values (4,331)
insert into @temp_cd values (5,332)
insert into @temp_cd values (6,333)
insert into @temp_cd values (7,444)

select * From @temp_cd

UPDATE @temp_cd
SET EMPNUMBER = 334 -- Your new value here
WHERE EMPid	 = 3 -- Provide the EmpID here

select * From @temp_cd```

Thanks,
Aje
My Blog | Dotnet Funda
• JBetancourt

Participant

1370 Points

608 Posts

I insist you should ask your question again, you are not providing enough details to understand what you are trying to do

-----------------
• Jayesh.Dalvi

Member

185 Points

297 Posts

Respected JBetancourt,asyed4u,a2h Sir,

I am describing the request in more detail please,

Please Note: Numbers should match from starting of the number and not between middle of any number or in between number.

EMPID |EMPNUMBER
1 |111

2 |222

3 |333

4 |444

5 |5555555

6 |5555666

7 |777

8 |88

9 |9

I need something like

Update emp set  EMPNUMBER= @empnumber where EMPID = 2

Where

EMPNUMBER in table is 222 for EMPID 2.

parameter @empnumber = 224

It should only update the record if it finds atleast some common content (more than one number ) between prsent EMPNUMBER in emp table and given parameter @empnumber in query for given
EMPID in single query which is known.(Provided EMPNUMBER length in emp table can be anything means not fixed).Limited upto varchar(50) data type.

Example 1.

Value in table for EMPNUMBER for EMPID 5 is 5555555

parameter @empnumber =5555554
Should update record for EMPID 5 with 5555554
(because it has common (555555) between these two values for EMPID 5.

Example 2.

Value in table for EMPNUMBER for EMPID 6 is 5555666
parameter @empnumber =5555677

Should update record for EMPID 6 with 5555677.
(because it has common (55556) between these two values.

Example 3,

Value in table for EMPNUMBER for EMPID 7 is 777
parameter @empnumber =777777

Should update record for EMPID 7 with =777777
(because it has common (777) between these two values.

Example 4,

Value in table for EMPNUMBER for EMPID 8 is 88
parameter @empnumber =88888

Should update record for EMPID 8 with =88888
(because it has common (88) between these two values.

Example 5,

Value in table for EMPNUMBER for EMPID 9 is 9
parameter @empnumber =9999

Should update record for EMPID 9 with 9999
(because it has common (9) between these two values.

Number length for EMPNUMBER can be anything but it should find some common content at least (one number common) between prsent number and parameter to update EMPNUMBER for given EMPID in query.

Thanks,

Regards,

Jayesh.(From India,Mumbai)

• Deepak Panch...

Contributor

2970 Points

1210 Posts

Aug 11, 2017 08:02 AM|Deepak Panchal|LINK

Hi Jayesh.Dalvi,

if we try to see your examples then we can find that the pattern is not fixed.

it can have any numbers of characters and you want to match it. like sometimes 3 digits get match and sometimes 5 digits get matched.

it is possible that when you try to match it you will get multiple results.

if possible for you then you can try to use 'Like' keyword in your update query.

`match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]  `

Reference:

LIKE (Transact-SQL)

or you can try to use LEFT Function.

`LEFT ( character_expression , integer_expression )  `

Reference:

LEFT (Transact-SQL)

you can try to use wildcard characters for matching it.

Regards

Deepak

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• Jayesh.Dalvi

Member

185 Points

297 Posts

Respected JBetancourt,asyed4u,a2h, Deepak Panchal Sir,

Thanks for every solution given by you to solve the request in different given situations.

Every solution has helped me to progress my knowledge towards writing MS SQL Query expression for my request.

Thanks to all of you,

Thanks ,

Regards,

Jayesh(From India,Mumbai)

• JBetancourt

Participant

1370 Points

608 Posts

As I see it makes no difference how many characters match so I will match only by the first character:

```Declare @temp_cd TABLE(
EMPid int,
EMPNUMBER int)

insert into @temp_cd values (1,111)
insert into @temp_cd values (2,222)
insert into @temp_cd values (3,333)
insert into @temp_cd values (4,444)
insert into @temp_cd values (5,5555555)
insert into @temp_cd values (6,5555666)
insert into @temp_cd values (7,777)
insert into @temp_cd values (8,88)
insert into @temp_cd values (9,9)

select * from @temp_cd

declare @empnumber  varchar(12)
set @empnumber=75555554

update @temp_cd set EMPNUMBER=@empnumber WHERE left(EMPNUMBER,1) = left(@empnumber,1)

select * from @temp_cd```

-----------------
• Jayesh.Dalvi

Member

185 Points

297 Posts