davidi1
asked on
SQL Query Help
Team -
I have a table with a field "Information" in which i would like to get only the first character of all the words.
Example:
"Information" fields contains 5 rows
1. What is my name?
2. Ohio is a nice place.
3. Oh My God !!!
4. Job Well done..,
5. Hearty Welcome to our chairman.
I'd like to run a query which will pull only the first character of each word in each row.
i.e.,
1.WIMN
2.OIANP
3.OMG
4.JWD
5.HWTOC
Requesting your help please
I have a table with a field "Information" in which i would like to get only the first character of all the words.
Example:
"Information" fields contains 5 rows
1. What is my name?
2. Ohio is a nice place.
3. Oh My God !!!
4. Job Well done..,
5. Hearty Welcome to our chairman.
I'd like to run a query which will pull only the first character of each word in each row.
i.e.,
1.WIMN
2.OIANP
3.OMG
4.JWD
5.HWTOC
Requesting your help please
select SUBSTR(first_name,1,1)name from employee
I think there isn't any function on SQL-Server for getting first character of every word. I think you need to create a user defined function for achieving this and then execute this query:
SELECT FirstCharOfEveryWord(Infor mation) as first_characters
FROM yourTable;
Where FirstCharOfEveryWord is the function you should create. I suppose the idea is to take the first character (by SUBSTR function) and then use a loop to treat every character (gotten also by SUBSTR) and everytime you find a space take next character. But probably you should also treat things like if there are more than one space together, what to do with punctuation signs (point, comma, ...) and things like this, so this function could not be easy.
SELECT FirstCharOfEveryWord(Infor
FROM yourTable;
Where FirstCharOfEveryWord is the function you should create. I suppose the idea is to take the first character (by SUBSTR function) and then use a loop to treat every character (gotten also by SUBSTR) and everytime you find a space take next character. But probably you should also treat things like if there are more than one space together, what to do with punctuation signs (point, comma, ...) and things like this, so this function could not be easy.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi davidi1,
try this:
try this:
SELECT *,
(SELECT UPPER(SUBSTRING(Information, n, 1))
FROM YourTable CROSS JOIN (SELECT row_number() over (order by object_id) AS n FROM sys.columns) AS num
WHERE ID = y.ID AND SUBSTRING(' ' + Information, n, 1) = ' ' AND n<LEN(Information) FOR XML PATH(''))
FROM YourTable AS y
You are obviously doing this as part of another process. I could not help wondering if you were implementing your own searching mechanism. In which case, I would highly recommend you looked into implementing a full text index.
>> first character of all the words. <<
You have to define what you mean by "word".
Only spaces??
Me personally, I would think something along these lines, *conceptually* (not necessarily directly coded this way):
Find the first/next alpha/numeric char, to start a "word"
All immediately following: alpha,numeric, *and underscores* are part of that "word" (./|~ etc are not)
First char that is NOT one of those ends the "word".
--Loop/repeat until all words are processed.
Is that right? Or do you want include other chars within a "word"?
You have to define what you mean by "word".
Only spaces??
Me personally, I would think something along these lines, *conceptually* (not necessarily directly coded this way):
Find the first/next alpha/numeric char, to start a "word"
All immediately following: alpha,numeric, *and underscores* are part of that "word" (./|~ etc are not)
First char that is NOT one of those ends the "word".
--Loop/repeat until all words are processed.
Is that right? Or do you want include other chars within a "word"?
SELECT left(field1,1), left(field2,1) from your_table_name