-1

I have a table with string filed for age of children that separate with ',' or '-' like this

enter image description here

I want to find items that contain '4' for example 4-9-14 , . i want to separate items with ',' or '-' then find '4'

ar.gorgin
  • 4,765
  • 12
  • 61
  • 100
  • 4
    The real solution here is fix your design – Thom A Apr 03 '21 at 11:20
  • Does this answer your question? [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – Thom A Apr 03 '21 at 11:22
  • Does this answer your question? [Passing a varchar full of comma delimited values to a SQL Server IN function](https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) – Thom A Apr 03 '21 at 11:23
  • What does "find 4" actually mean? Values where it has the character `'4'`? If so, why do you need to split the data? What have ***you*** tried to solve the problem yourself? Why didn't it work? Tthough that doesn't change my point; fix the design. Really, as well, you should be storing the date of birth, rather than an age. An age value becomes out of date as soon as it's entered, where as a DOB allows you to calculate the age of someone for *any* given date. – Thom A Apr 03 '21 at 11:30

3 Answers3

4

You really should fix the data model. In the absence of that, like might be your best choice. I am thinking:

where ' ' + replace(replace(ageOfChildren, ',', ' '), '-', ' ') + ' ' like '% 4 %'

This changes the delimiter to ' ' by replacing all commas and hyphens. It then looks for ' 4 ' after the replacement.

Note the following:

  • Storing multiple values in a string is a bad idea.
  • Storing numbers in a string is a bad idea.
  • Storing ages is a bad idea, because they literally change every day.

You should have a separate table with one row per child and a date that stores the date of birth.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If all you want to do is find rows where the AgeOfChildren column contains the character '4', this query should do it SELECT * FROM [table] WHERE AgeOfChildren LIKE '%4%'

Samuel Adeshina
  • 121
  • 3
  • 1
    What if the child is 14? `'14'` is `LIKE '%4%'`. – Thom A Apr 03 '21 at 11:23
  • I thought about this, and the OP says they need the character '4'. This is more like a date problem. The data type for the column should be created appropriately and then you can extract the value you need using a better interface. – Samuel Adeshina Apr 03 '21 at 11:26
  • @Larnu It will find that. % is like a wild card. – Anuj Sharma Apr 03 '21 at 11:26
  • I know... I *suppose* "find '4'" could be read several ways, including values that contain the number 4, rather than the child is the age 4; but then if that is true there's no need to split the data as the OP describes. At best, the question is unlclear. – Thom A Apr 03 '21 at 11:28
  • it find 4 14 but i want to find only 4 for example 4-9 or 12,5,4 – ar.gorgin Apr 03 '21 at 12:12
0

If you want to first seperate items with comma and dash, then find items with AgeOfChildren 4 :

SELECT * FROM [table] 
WHERE AgeOfChildren NOT LIKE '%,%' 
AND 
AgeOfChildren NOT LIKE '%-%' 
AND 
AgeOfChildren LIKE '%4%'
Anuj Sharma
  • 409
  • 2
  • 8