Inline query to perform specific operation

• thesqlcoder

  Mr or Mrs. 500

  Points: 572

  More actions

  May 18, 2016 at 11:18 pm

  #327160

  Hi,

  I have a special requirement in which if a string contains any numeric and character combination then there should be space in between.

  Eg. string 'this 12A string' should be 'this 12 A string'

  'this is test 1265B string' should be 'this is test 1265 B string'

  'is test BB16AA string' should be 'is test BB16 AA string'

  '1A string' should be '1 A string'

  '2B' should be '2 B'

  I have created temp table with this rows.

  SELECT * INTO #SearchRow from (

  SELECT 'this 12A string' AS CompanyAddress

  UNION

  SELECT 'this is test 1265B string' AS CompanyAddress

  UNION

  SELECT 'is test BB16AA string' AS CompanyAddress

  UNION

  SELECT 'this is test 7B string' AS CompanyAddress

  UNION

  SELECT 'this 7 B string' AS CompanyAddress

  UNION

  SELECT 'this is test 6C 66C 555CC string' AS CompanyAddress

  UNION

  SELECT 'this is test 2276A string' AS CompanyAddress

  UNION

  SELECT '1A string' AS CompanyAddress

  UNION

  SELECT '2B' AS CompanyAddress

  ) a

  SELECT * FROM #SearchRow

  I can easily archive by creating sub-function or sub stored procedure but for the performance reason, i want it perform with the inline query.

  Thanks for the help in advance.

• thesqlcoder

  Mr or Mrs. 500

  Points: 572

  More actions

  May 19, 2016 at 1:33 am

  #1880105

  I found it very simple way as below.

  SELECT CompanyAddress AS OriginalString,

  SUBSTRING(CompanyAddress, 1, PATINDEX('%[0-9][a-z]%', CompanyAddress)) AS FirstPart,

  SUBSTRING(CompanyAddress, PATINDEX('%[0-9][a-z]%', CompanyAddress)+ 1,LEN(CompanyAddress)) AS SecondPart,

  SUBSTRING(CompanyAddress, 1, PATINDEX('%[0-9][a-z]%', CompanyAddress)) + ' ' + SUBSTRING(CompanyAddress, PATINDEX('%[0-9][a-z]%', CompanyAddress)+ 1,LEN(CompanyAddress)) AS UpdatedString

  FROM #SearchRow WHERE CompanyAddress like '%[0-9][a-z]%'

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 19, 2016 at 5:13 am

  #1880135

  The STUFF function would be good for this too:

  SELECT *, CompanyAddress = STUFF(CompanyAddress,pos+1,0,' ')

  FROM #SearchRow

  CROSS APPLY (SELECT pos = PATINDEX('%[0-9][a-z]%', CompanyAddress)) x

  WHERE x.pos > 0

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 19, 2016 at 8:04 am

  #1880252

  This will allow to handle multiple occurrences. It uses a pattern splitter that is explained in here: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

  And string concatenation explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

  SELECT *,

  LTRIM((SELECT CASE WHEN ps.Matched = 0 THEN ' ' ELSE '' END + ps.Item

  FROM dbo.PatternSplitCM(sr.CompanyAddress, '%[0-9]%' ) ps

  ORDER BY ps.ItemNumber

  FOR XML PATH(''),TYPE).value('./text()[1]', 'varchar(max)'))

  FROM #SearchRow sr;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2