October 29, 2009 at 6:31 pm
Hi,
i need to know how via a stored procedure i can insert / character between two numeric values separated by one or more space ' ' character in a column.
either side of this / should be a [0-9] character should be numeric like '%[0-9]%' / '%[0-9]%'
Also how can i find out the number if occurences of a specific character within a field.
For instance how many times let's say / character occurs with in 199/112 DRY DOCK ROAD 23/345/Elster
eg:-
Unit 199 112 DRY DOCK ROAD 23 345 Elster
Unit 28 276 280 DORSET ROAD
ABBACUS 276 280 DORSET ROAD
Unit 18 125 ST Hela-BERWICK ROAD
18 125 Augusta St
ABC 23 34 Normsnd 45 36 Glass Ave
Unit 199/112 DRY DOCK ROAD 23/345 Elster
Unit 28/276-280 DORSET ROAD
ABBACUS 276/280 DORSET ROAD
Unit 18/125 ST Hela-BERWICK ROAD
18/125 Augusta St
ABC 23/34 Normsnd 45/36 Glass Ave
/****** Object: Table [dbo].[Test_Format_Addresses] Script Date: 10/30/2009 11:01:40 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_Format_Addresses]') AND type in (N'U'))
DROP TABLE [dbo].[Test_Format_Addresses]
GO
--===== Create the test table with
CREATE TABLE [dbo].[Test_Format_Addresses](
[cust_no] [varchar](15) NOT NULL,
[Address1] [varchar](100) NULL,
[Address2] [varchar](150) NULL,
CONSTRAINT [PK_Test_Format_Addresses] PRIMARY KEY CLUSTERED
(
[cust_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--===== Insert the test data into the test table
INSERT INTO [dbo].[Test_Format_Addresses]
(Cust_no,Address1,Address2)
SELECT'0001','Unit 199 112 DRY DOCK ROAD 23 345 Elster',' ' UNION ALL
SELECT'0002','Unit 28 276 280 DORSET ROAD ',' ' UNION ALL
SELECT'0003','ABBACUS 276 280 DORSET ROAD ',' ' UNION ALL
SELECT'0004','Unit 18 125 ST Hela-BERWICK ROAD ',' ' UNION ALL
SELECT'0005','18 125 Augusta St','' UNION ALL
SELECT'0006','ABC 23 34 Normsnd 45 36 Glass Ave',''
/****** Script for SelectTopNRows command from SSMS ******/
SELECT *
FROM [dbo].[Test_Format_Addresses]
October 29, 2009 at 10:17 pm
Thanks Abdallah, PMP, MCTS,
Have a solution for counting occurence of a specific character in a column/string.
Simply created a usd function with 2 paramters named @Search_For this can be either / - a 3 or anything else one needs to search for.
and @string can be a column/string being searced.
Create FUNCTION [dbo].[OCCURS](@search_for VARCHAR(MAX),@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN (LEN(LTRIM(RTRIM(@string))) - LEN(REPLACE(LTRIM(RTRIM(@string)), @search_for, '')))
END
No progress on original/main Question yet.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply