I am trying to update an integer value of a column based on partial data contained in a VARCHAR column

Column A (VAR CHAR) contains a UNC Path eg.

lab03-app01\66\2016\10\3\LAB03-REC01\4989\1_6337230127359919046_6337230127366210371.wav

Within the UNC Path is an index number 4989.

I need to be able to update Column B (INT) to be equal to the value of Index Number located in Column A.

Is this possible?

This would be much easier with CLR and regular expressions.

One way of doing what you need in TSQL is below (demo).

DECLARE @T TABLE (A VARCHAR(255), B INT NULL);

INSERT INTO @T(A) VALUES ('lab03-app01\66\2016\10\3\LAB03-REC01\4989\1_6337230127359919046_6337230127366210371.wav')

UPDATE T
SET B = CAST(REVERSE(SUBSTRING(ReverseA, FinalSlash, PenultimateSlash - FinalSlash)) AS INT)
FROM @T T
CROSS APPLY (SELECT REVERSE(A))  ca1(ReverseA)
CROSS APPLY (SELECT 1 + CHARINDEX('\', ReverseA))  ca2(FinalSlash)
CROSS APPLY (SELECT CHARINDEX('\', ReverseA, FinalSlash))  ca3(PenultimateSlash);

SELECT *
FROM @T;

Trying to write an Integer value through the js socket node, but can not do that

Am trying to post some string from my tcp client to tcp server(Both were implemented using NodeJS). Once I receive message from client I need to write some integer value in the same socket. But when I tried writing the integer value, am getting an ex

PHP - MYSQL trying to store the integer value

When I try to execute this is AND getting the following error: Error: Incorrect integer value: 'Age' for column 'age' at row 1 MYSQL --> I have created a database abc inside created a table with name ee and in the table i have created a field "age

VHDL - updating an integer value for error checking

I've started to try to get my test benches to do some error checking for me. In the below model I use a process to check that two values are equal and if so change a boolean expression to true. The problem being that it does not change at all. The in

Filter the string / float / integer values in the columns (Pandas)

How can I filter only string values / integer / float values in one column in pandas data frame like below? SIC 1 246804 2 135272 3 898.01 4 3453.33 5 shine 6 add 7 522 8 Nan 9 string 10 29.11 11 20 You can use the outputs from pd.to_numeric and bool

Replace values in some rows based on other data mappings

I have a table (d.tab) with question–answer pairs from a survey. Some of these are single-choice answers, some are multiple-choice. I want to look up the text value of the single-choice answer from its numerical value. For that, I have a lookup table

Change the float values to integer values and then concatenate into a pandas data frame

I have a dataframe named "sample" which has three columns: "birthDay", "birthMonth" and "birthYear" and containing float values as in following picture: I want to add new column "dateOfBirth" and to have e

How to get integer values from an array based on the first java digits

I have int number value and int[] numberArray. I want to get all values from numberArray that starts with number. What I am trying to do is for (int i=0;i<numbers.length;i++){ if(numberArray[i] "starts with" number){ System.out.print(numberAr

Update the null values of all columns dynamically in the hive

I have a data set of 75 columns and 411274 rows of which many of the entries are null or NA I want to replace all those values with Data_not_available is there a way to do it dynamically rather than entering each column name to check and update the d

How to update the timestamp values in the column by adding a specific time (seconds) to the existing timestamp using mysql?

I am using mysql and pma. I have a table mytable and a column time, storing ~17K individual values, i.e. timestamps (integers). I need to update each by adding 962758 to each timestamp. What does the SQL command for that look like? SELECT (*) FROM `m

Related Articles