daily pastebin goal
24%
SHARE
TWEET

Untitled

a guest May 16th, 2018 75 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ElectricianId | Company     | TelNo     | Mobile   | Addr1        | Postcode
  2. 123           | Sparky 1    | 01234567  | 0789078  | 42 lower ave | Ex2345
  3. 124           | Sparky 2    | 01235678  | 0777777  | 1 Street     | Ta6547
  4. 125           | Sparky 3    | 05415644  | 0799078  | 4 Air Road   | Gl4126
  5.    
  6. PainterId     | Company     | TelNo     | Mobile   | Addr1        | Postcode
  7. 333           | Painter 1   | 01234568  | 07232444 | 4 Higher ave | Ex2345
  8. 334           | Painter 2   | 01235679  | 07879879 | 5 Street     | Ta6547
  9. 335           | Painter 3   | 05415645  | 07654654 | 5 Sky Road   | Gl4126
  10.    
  11. ClientId | Name            | TelNo     | Mobile   | Addr1        | Postcode
  12. 100333   | Mr Chester      | 0154 5478 | 07878979 | 9 String Rd  | PL41 1X
  13. 100334   | Mrs Garrix      | 0254 6511 | 07126344 | 10 String Rd | PL41 1X
  14. 100335   | Ms Indy Pendant | 0208 1154 | 07665654 | 11 String Rd | PL41 1X
  15.    
  16. SET @searchTerms = LTRIM(RTRIM(
  17.         REPLACE(
  18.         REPLACE(
  19.         REPLACE(
  20.         REPLACE(
  21.         REPLACE(
  22.         REPLACE(
  23.         REPLACE(
  24.         REPLACE(
  25.         REPLACE(
  26.         REPLACE(
  27.         REPLACE(
  28.         REPLACE(
  29.         REPLACE(
  30.         REPLACE(
  31.         REPLACE(
  32.         REPLACE(
  33.         REPLACE(
  34.         REPLACE(LTRIM(RTRIM(@searchTerms)), ',', ' '),
  35.         '[', ''),
  36.         ']', ''),
  37.         '#', ''),
  38.         '&', ''),
  39.         ';', ''),
  40.         '?', ''),
  41.         '`', ''),
  42.         '''', ''),
  43.         '*', ''),
  44.         '"', ''),
  45.         '<', ' '),
  46.         '>', ' '),
  47.         '-', ' '),
  48.         '(', ' '),
  49.         ')', ' '),
  50.         '', ' '),
  51.         '/', ' ')))
  52.  
  53.         SET @searchTerms = REPLACE(@searchTerms, ' ', ',')
  54.  
  55.         DECLARE @SearchTerm AS nvarchar(50);
  56.  
  57.         DECLARE @DevelopmentCursor AS CURSOR;
  58.         SET @DevelopmentCursor = CURSOR
  59.         FOR
  60.         SELECT
  61.           *
  62.         FROM general.Csvtoquery(@searchTerms)
  63.         WHERE value != ''
  64.    
  65. INSERT INTO #tempsearchtable (EntityId, Name, LongName, EntityType)
  66.             SELECT
  67.                 tc.ClientId,
  68.                 tc.Title + ' ' + tc.FirstName + ' ' + tc.LastName,
  69.                 tc.Title + ' ' + tc.FirstName + ' ' + tc.LastName + ', ' + COALESCE(a.NameOrNumber, '') + ', ' + COALESCE(a.Street, '') + ', ' + COALESCE(a.Town, '') + ', ' + + ', ' + COALESCE(a.County, '') + ', ' + COALESCE(a.Postcode, '') + ', ' + COALESCE(a.Country, '')  + ', ' + COALESCE(tc.EmailAddress, '')  + ', ' + COALESCE(REPLACE(tc.Telephone, ' ', ''), '')  + ', ' + COALESCE(REPLACE(tc.Mobile, ' ', ''), ''),
  70.                 'Client'
  71.             FROM
  72.                 dbo.Clients tc
  73.             LEFT JOIN
  74.                 dbo.[Address] a ON tc.AddressId = a.AddressId
  75.             WHERE
  76.                 tc.FirstName LIKE '%' + @SearchTerm + '%'
  77.                 OR tc.LastName LIKE '%' + @SearchTerm + '%'
  78.                 OR tc.EmailAddress = @SearchTerm
  79.                 OR REPLACE(tc.Telephone, ' ', '') LIKE '%' + @SearchTerm + '%'
  80.                 OR REPLACE(tc.Mobile, ' ', '') LIKE '%' + @SearchTerm + '%'
  81.                 OR a.NameOrNumber LIKE '%' + @SearchTerm + '%'
  82.                 OR a.Street LIKE '%' + @SearchTerm + '%'
  83.                 OR a.Postcode LIKE '%' + @SearchTerm + '%'
  84.                 OR a.County LIKE '%' + @SearchTerm + '%'
  85.                 OR a.Town LIKE '%' + @SearchTerm + '%'
  86.                 OR a.Country LIKE '%' + @SearchTerm + '%'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top