How to Append Character to Field Values

Question

How to Append Character to Field Values

Ten Centuries

Points: 1026

October 16, 2022 at 12:55 pm

A community member called Jeff Morden helped with a query that removes characters from a field value, see https://www.sqlservercentral.com/forums/topic/how-to-remove-character-and-carriage-return-from-fields#post-4099382

Can someone help modify the code to include www. when it doesn't exists. For example, the query

SELECT  tt.homepage_url
        ,websiteurl = LEFT(v1.RightString,COALESCE(NULLIF(CHARINDEX('/',v1.RightString)-1,-1),150))
   FROM #tmpTable tt
  CROSS APPLY (VALUES(SUBSTRING(homepage_url,CHARINDEX('//',homepage_url)+2,150)))v1(RightString)
;

Will produce the following results:

wwwin

You notice that http://navico.com and https://zeb-consulting.com is not prepended with www, as a result the code removes http://. However, as well as removing http:// I would like www prepended (or is the word prefaced? 🙂 with www.

So, can someone help modify the code such to add www where it doesn't exists?

Sample Data

CREATE TABLE tmpTable (
     homepage_url nvarchar(150),
     websiteurl varchar(100))
    
 INSERT tmpTable VALUES
 (N'http://www.opisnet.com','www.opisnet.com'),
 (N'http://www.ebookers.com','www.ebookers.com'),
 (N'http://navico.com','navico.com'),
 (N'https://zeb-consulting.com','www.zeb.de'),
 (N'http://www.hobbycraft.co.uk','www.hobbycraft.co.uk'),
 (N'http://www.parliament.uk/lords','www.parliament.uk/lords'),
 (N'https://www.intermed.de/','www.intermed.de/'),
 (N'http://www.iac.com','www.iac.com'),
 (N'https://www.esteve.es','www.esteve.com/'),
 (N'http://www.kornferry.com/','www.kornferry.com')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

You could nest the current formula inside an additional CONCAT function and test for the existence of the lead characters

 SELECT  tt.homepage_url
        ,websiteurl = concat(iif(left(v1.RightString, 4)=N'www.', null, N'www.'),
                      LEFT(v1.RightString,COALESCE(NULLIF(CHARINDEX(N'/',v1.RightString)-1,-1),150)))
   FROM #tmpTable tt
  CROSS APPLY (VALUES(SUBSTRING(homepage_url,CHARINDEX(N'//',homepage_url)+2,150))) v1(RightString)
;

This reply was modified 2 years, 1 month ago by Steve Collins. Reason: LEFT(String, 4) is a better comparison than CHARINDEX

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

One of my suggestions in one of the many other threads you have created on this subject was to fix the data. You can write all the code you want but bad data will always win out...

Assuming that every site must be preceded with 'www' is also incorrect. A good example is Microsoft's online documentation which is now located at https://learn.microsoft.com. How about one of the other sites where we can get help https://forums.sqlteam.com.

To answer your question - how do you determine if 'www.' exists in a string - and if it doesn't, add it?

SELECT someColumn = IIF(somestring NOT LIKE 'www.%', CONCAT('www.', someString), someString)

-- Or using CASE
SELECT someColumn = CASE WHEN someColumn NOT LIKE 'www.%' THEN CONCAT('www.', someString) ELSE someString END

To simplify your code - here is a hint: Add another CROSS APPLY using the results from v1(RightString) to calculate the value for websiteurl - then use another CROSS APPLY to prepend the 'www.' to the results from websiteurl. At that point you can then use the final result in any other expressions as needed.

This reply was modified 2 years, 1 month ago by Jeffrey Williams.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

I can also tell you for sure that the "community member"s name was not Jeff Morden. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)