November 2, 2015 at 5:54 pm
I have a table of data with lines of various lengths. An example is
A Smith - Give #12345# Sydney City
B Jones and S Jones - Give #876543# Washington
I am trying to work out how to create a new column with just the number between the # symbols. The number would be between 5 and 9 numbers.
Any suggestions on the easiest way to do this in a view?
November 2, 2015 at 6:19 pm
les.61 (11/2/2015)
I have a table of data with lines of various lengths. An example isA Smith - Give #12345# Sydney City
B Jones and S Jones - Give #876543# Washington
I am trying to work out how to create a new column with just the number between the # symbols. The number would be between 5 and 9 numbers.
Any suggestions on the easiest way to do this in a view?
It's pretty easy to do but we need a couple more pieces of information the most important of which is what criteria will you use against the view so that you know which row the data came from? There has to be another column returned in the view.
Also, you need to realize that the numbers would likely come back in single rows, one each. If you want actual separate columns per row, can you absolutely guarantee that no more than 9 numbers will ever be included?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2015 at 6:20 pm
Ah... almost forgot. It's usually helpful to those trying to help if you provide readily consumable data and a little DDL. Please see the article at the first link in my signature line below for the right way to do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2015 at 6:36 pm
I have a table of data with lines of various lengths. An example is
A Smith - Give #12345# Sydney City
B Jones and S Jones - Give #876543# Washington
I am trying to work out how to create a new column with just the number between the # symbols. The number would be between 5 and 9 numbers.
Any suggestions on the easiest way to do this in a view?
The lines are 1 column of data So how about we call it "Narrative". The end result I am after would be a column called say "CodeNumber" which would look like
12345
876543
Does that make sense?
November 2, 2015 at 7:05 pm
les.61 (11/2/2015)
I have a table of data with lines of various lengths. An example is
A Smith - Give #12345# Sydney City
B Jones and S Jones - Give #876543# Washington
I am trying to work out how to create a new column with just the number between the # symbols. The number would be between 5 and 9 numbers.
Any suggestions on the easiest way to do this in a view?
The lines are 1 column of data So how about we call it "Narrative". The end result I am after would be a column called say "CodeNumber" which would look like
12345
876543
Does that make sense?
Narrative it shall be.
Get the DelimitedSplit8K function from the following link. The header in the function tells how to use it to solve your problem. You could add criteria to return digits only using LIKE. Don't use IsNumeric because it does NOT work as a "digits only" function.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
I worry about you using it in a view because the return (the Item column of the function) will be a calculated column and the view will need to materialize ALL rows in whatever table you have before any filter criteria can be applied.
Makes sense?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2015 at 10:07 pm
May be missing the point completely, but from what you have provided, this may get you started:
DECLARE @Col varchar(500)
SET @Col = 'A Smith - Give #12345# Sydney City'
SELECT LEFT(RIGHT(RTRIM(@Col), Len(@Col) - (CHARINDEX('#', @Col))),CHARINDEX('#',RIGHT(RTRIM(@Col), Len(@Col) - (charindex ('#', @Col))-1)))
CodeNumber
You will need to change this in light of a table and column name. I would assume you would begin with an ALTER TABLE..... to create the new column.
...
November 2, 2015 at 10:18 pm
Many thanks 'happygeek'. This seems to work. Just have to sit down and work through the logic as a learning exercise.
:-P:-P:-P:-P:-P
November 2, 2015 at 11:35 pm
Hmmm... HappyGeek's method will certainly do one number per row very efficiently but I thought you said there could be 5 to 9 such numbers per row. Did I read that wrong and what you really meant only one number per row, which will have between 5 and 9 digits?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2015 at 11:52 pm
Yes, one number between 5 and 9 digits. Sorry if I did not explain properly. New at this stuff.
November 5, 2015 at 2:37 pm
Just as an FYI, you can use a CROSS APPLY in these types of situations to assign meaningful alias names to intermediate values, which can sometimes make the SELECT logic a little clearer and can give you additional capabilities. For example, the code below can gracefully handle (a) only one # sign in the column and/or (b) no # in the column.
SELECT
combined_column,
CASE WHEN combined_column_first# = 0
THEN NULL
ELSE SUBSTRING(combined_column, combined_column_first# + 1, combined_column_number_length)
END AS embedded_number
FROM (
SELECT 'A Smith - Give #12345# Sydney City' AS combined_column UNION ALL --both #s, normal format
SELECT 'B Jones and S Jones - Give #876543# Washington' UNION ALL --both #s, normal format
SELECT 'P Mackey and M Mackey - Give #87654334 Monument' UNION ALL --one # only
SELECT 'B Cheapskate and R Cheapskate - Give nothing! Anytown' -- --no # at all
) AS test_data
CROSS APPLY (
SELECT CHARINDEX('#', combined_column) AS combined_column_first#
) AS assign_alias_names1
CROSS APPLY (
SELECT CASE WHEN combined_column_first# = 0 THEN 0 ELSE PATINDEX('%[^0-9]%',
SUBSTRING(combined_column + '.', combined_column_first# + 2, 8000)) END AS combined_column_number_length
) AS assign_alias_names2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 5, 2015 at 4:38 pm
Here is some old school solution.
Should make the logic crystal clear.
And it's set based, works on a table, so may be easily made into a view.
DECLARE @Table TABLE (
Col varchar(500)
)
DECLARE @Delimiter VARCHAR(20)
SET @Delimiter = '#'
INSERT INTO @Table (Col)
SELECT 'A Smith - Give #12345# Sydney City'--both #s, normal format
UNION ALL
SELECT 'B Jones and S Jones - Give #876543# Washington' --both #s, normal format
UNION ALL
SELECT 'P Mackey and M Mackey - Give #87654334 Monument' --one # only
UNION ALL
SELECT 'B Cheapskate and R Cheapskate - Give nothing! Anytown' --no # at all
SELECT Col, Delimiter, DL, SUBSTRING(S1, 1, i2-1 ) AS DesiredCode
FROM (
SELECT Col, Delimiter, DL, S1, NULLIF(CHARINDEX(Delimiter, S1), 0) AS i2
FROM (
SELECT Col, Delimiter, DL, SUBSTRING(Col, i1+DL, LEN(COL) ) AS S1
FROM (
SELECT Col, @Delimiter Delimiter, CHARINDEX(@Delimiter, COL) i1, DATALENGTH(@Delimiter) AS DL
FROM @Table
)T1
) T2
) T3
GO
Following option includes the logic of picking up the 1st non-numeric character as the end of the code when there is no 2nd delimiter in the string (not sure if it's a good idea):
DECLARE @Table TABLE (
Col varchar(500)
)
DECLARE @Delimiter VARCHAR(20)
SET @Delimiter = '#'
INSERT INTO @Table (Col)
SELECT 'A Smith - Give #12345# Sydney City' --both #s, normal format
UNION ALL
SELECT 'B Jones and S Jones - Give #876543# Washington' --both #s, normal format
UNION ALL
SELECT 'P Mackey and M Mackey - Give #87654334 Monument' --one # only
UNION ALL
SELECT 'B Cheapskate and R Cheapskate - Give nothing! Anytown' --no # at all
SELECT Col, Delimiter, DL, SUBSTRING(S1, 1, i2-1 ) AS DesiredCode
FROM (
SELECT Col, Delimiter, DL, S1, ISNULL(NULLIF(CHARINDEX(Delimiter, S1), 0), PATINDEX('%[^0-9]%', S1)) AS i2
FROM (
SELECT Col, Delimiter, DL, SUBSTRING(Col, i1+DL, LEN(COL) ) AS S1
FROM (
SELECT Col, @Delimiter Delimiter, CHARINDEX(@Delimiter, COL) i1, DATALENGTH(@Delimiter) AS DL
FROM @Table
)T1
) T2
) T3
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply