SQL field interception content problem, need to capture the contents of the location is not fixed-CodePudding

Have a fixed string in field to intercept a content after
For example,
1.12345678 id100056
2.1234 id100058
3.234567 id100057
Need to capture specific Numbers after the ID string in each field, fixed digits
Hope is the result of the
100056
100058
100057
Access to digital hope and another update association, eligible update numerical
Written up step
Still need to put a table in the first match

CodePudding user response:

 
The create table # t (id int, x varchar (50)) 

Insert into # t (id, x) 
Select 1, '12345678 id100056' union all 
Select 2, '1234 id100058' union all 
Select 3, '234567 id100057' 


Select id, x, x2=cast (substring (x, charindex (' id ', x) + 2, 50) as an int) 
The from # t 

/* 
Id x x2 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 12345678 id100056 100056 
2 1234 id100058 100058 
3 234567 id100057 100057 

(3 rows affected) 
*/

CodePudding user response:

The

reference 1/f, started the first reply:

 
The create table # t (id int, x varchar (50)) 

Insert into # t (id, x) 
Select 1, '12345678 id100056' union all 
Select 2, '1234 id100058' union all 
Select 3, '234567 id100057' 


Select id, x, x2=cast (substring (x, charindex (' id ', x) + 2, 50) as an int) 
The from # t 

/* 
Id x x2 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 12345678 id100056 100056 
2 1234 id100058 100058 
3 234567 id100057 100057 

(3 rows affected) 
*/

Thank you, but I demand is from a list of the characters of a field to capture
And then compared with other tables?

CodePudding user response:

Associative table, use the right function
Field=right (field 1, len (field))

CodePudding user response:

The test code as follows; According to the field of vc updating in table test02_update id;

 create table test02 (id int, vc varchar (200)); 
Insert into test02 
Select 1, '12345678 id100056' 
Union all 
Select 2, '1234 id100058' 
Union all 
Select 3, '234567 id100057' 

The create table test02_update (id_update int, vc varchar (200)); 
Insert into test02_update 
Select 5, '100056' 
Union all 
Select 6, '100058' 
Union all 
Select 7, '100057' 

Update a set Anderson d_update=b.i d 
The from test02_update as a 
Left join test02 as b on the right (b.v c, 6)=Dr. C 

Select * from test02_update;