Take a value from an SQL table cell and insert it in another cell

Question

I have an old SQL database (Microsoft's SQL Server) with thousands of rows that contains data as follows:

ID      urlString

1      page.aspx?pageID=34
2      page.aspx?pageID=163
3      page.aspx
4      page.aspx?pageID=23

I've added a new column (pageID) to the database. I want to create an UPDATE query to copy the pageID from the URLstring and insert it in the new column (pageID) as follows. If there is no pageID I want to add 0. How can I accomplish that?

ID      URLstring                pageID

1      page.aspx?pageID=34        34
2      page.aspx?pageID=163       163
3      page.aspx                  0
4      page.aspx?pageID=23        23

what rdbms are you working with? string manipulation functions usually have different formats in different databases. — Zohar Peled, Jul 17 '16 at 11:34
Can PageId be followed with other query string values? (I mean, can the url be `page.aspx?pageID=34&something=value`)? — Zohar Peled, Jul 17 '16 at 11:40
No but it could be page.aspx?something=value&pageID=34. pageID is always the last. — Gloria, Jul 17 '16 at 11:45

Hassan · Answer 1 · 2016-07-17T11:45:30.007

0

UPDATE YourTable
SET pageID= 
SUBSTRING(urlString,CHARINDEX('=', urlString)+1,CHARINDEX('=', urlString))

To have the 0 value

UPDATE YourTable
SET pageID= 
CASE 
        WHEN CHARINDEX('=', urlString) > 0 THEN 
            SUBSTRING(urlString,CHARINDEX('=', urlString)+1,CHARINDEX('=', urlString))
        ELSE 0
    END

edited Jul 17 '16 at 11:45

answered Jul 17 '16 at 11:41

Hassan

1,413
1
10
12

it'll set final value to newcoulmn – Karthik AMR Jul 17 '16 at 11:43

Esperento57 · Answer 2 · 2016-07-17T11:54:59.757

0

I try it:

update yourtable
set pageID=
case when
substring(URLstring, charindex('=', URLstring) +1, len(URLstring) -  charindex('=', URLstring)    )
=URLstring then '0'
else 
substring(URLstring, charindex('=', URLstring) +1, len(URLstring) -  charindex('=', URLstring)    )
end

edited Jul 17 '16 at 11:54

answered Jul 17 '16 at 11:44

Esperento57

16,521
3
39
45

score 0 · Answer 3 · answered Jul 17 '16 at 11:46

0

please try the following

SELECT urlString,  cast(REVERSE(SUBSTRING(REVERSE(urlString),0,CHARINDEX('=',REVERSE(urlString)))) as smallint) AS [pageID]

Hope this helps.

answered Jul 17 '16 at 11:46

Prashant Majhwar

170
1
9

score 0 · Answer 4 · edited May 23 '17 at 12:00

Well, whichever SQL language variant the database uses will make a difference here. (i.e. SQLplus, NOSQL, etc.) However, it shouldn't be toooooooooo complicated. Assuming that both columns are INTs, you could probably just do something like so

UPDATE table_name
SET pageID = URLstring;

Here is some other sources for additional information.

A similar stack overflow question: Copy data from one column to other column (which is in a different table)

This is a TutorialsPoint webpage on the matter: http://www.tutorialspoint.com/sql/sql-update-query.htm (TutorialsPoint is usually one of my first stops for any programming-related knowledge, it's quite a valuable website.)

This is a w3schools webpage on the matter: http://www.w3schools.com/sql/sql_update.asp

Hope you get everything figured out!

score 0 · Answer 5 · answered Jul 17 '16 at 11:53

You can achieve this by the PARSENAME using the following UPDATE query:

UPDATE TestTable 
SET pageID = CASE WHEN ISNUMERIC(PARSENAME(REPLACE(URLstring, '=', '.'), 1)) = 1 THEN PARSENAME(REPLACE(URLstring, '=', '.'), 1)
            ELSE 0 END

Sample execution with the given sample data

DECLARE @TestTable TABLE (ID INT, URLstring VARCHAR (200), PageID INT);

INSERT INTO @TestTable (ID, URLstring, pageID) VALUES
(1, 'page.aspx?pageID=34'  , NULL ),
(2, 'page.aspx?pageID=163' , NULL ),
(3, 'page.aspx'            , NULL ),
(4, 'page.aspx?pageID=23'  , NULL );

UPDATE @TestTable 
SET pageID = CASE WHEN ISNUMERIC(PARSENAME(REPLACE(URLstring, '=', '.'), 1)) = 1 THEN PARSENAME(REPLACE(URLstring, '=', '.'), 1)
            ELSE 0 END

So the SELECT * FROM @TestTable will result as:

ID  URLstring              PageID
------------------------------------
1   page.aspx?pageID=34    34
2   page.aspx?pageID=163   163
3   page.aspx              0
4   page.aspx?pageID=23    23

this will fail if the query string contains more then 4 parameters. — Zohar Peled, Jul 18 '16 at 06:13

score 0 · Answer 6 · answered Jul 18 '16 at 06:11

Using CASE, PATINDEX and SUBSTRING it's fairly easy to extract the page id from the url.

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T As TABLE 
(
    Id int IDENTITY(1,1), 
    URLString varchar (40), 
    PageId int
)

INSERT INTO @T (URLString) VALUES
('page.aspx?blabla=yadayada&pageID=34'),
('page.aspx?pageID=163'),
('page.aspx'),
('page.aspx?pageID=23')

Update statement:

UPDATE @T
SET PageId =    CAST(
                    CASE WHEN PATINDEX('%pageID=%', URLString) > 0 THEN
                        SUBSTRING(URLString, PATINDEX('%pageID=%', URLString) + 7, LEN(URLString))
                    ELSE
                        '0'
                    END
                 As int)

Verification:

SELECT Id, URLString, PageId 
FROM @T

Results:

Id          URLString                                PageId
----------- ---------------------------------------- -----------
1           page.aspx?blabla=yadayada&pageID=34      34
2           page.aspx?pageID=163                     163
3           page.aspx                                0
4           page.aspx?pageID=23                      23

Take a value from an SQL table cell and insert it in another cell

6 Answers6